11g New Feature: PIVOT I do have an interview tomorrow. Woohoo!

So my go at the PIVOT operator, "new" in 11g.

pivot_clause
Datawarehouse Guide
SQL Reference Examples
Arup Nanda's Example

My example.

Let's create some data first:

CREATE TABLE transaction_types
(
  transactiontypecode VARCHAR2(10)
    CONSTRAINT pk_transactiontypecode PRIMARY KEY
);

INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'DEBIT' );
INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'CREDIT' );

CREATE TABLE transactions
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  transactiontypecode
    CONSTRAINT fk_ttcode_transactions REFERENCES transaction_types( transactiontypecode )
    CONSTRAINT nn_ttcode_transactions NOT NULL,
  amount NUMBER(16,2)
    CONSTRAINT nn_amount_trans NOT NULL
    CONSTRAINT ck_amount_trans CHECK ( amount >= 0 ),
  date_created DATE DEFAULT SYSDATE
    CONSTRAINT nn_datecreated_trans NOT NULL
);

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 1,
    'DEBIT',
    44.44 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 2,
    'DEBIT',
    20.34 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 3,
    'CREDIT',
    5.60 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 4,
    'DEBIT',
    67 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 5,
    'DEBIT',
    234.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 6,
    'CREDIT',
    76.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 7,
    'DEBIT',
    3.45 );

So what's the big deal with PIVOT? I'm not sure yet other than it's something new and new is cool.

Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way:

SELECT
  TRUNC( date_created ) date_created,
  SUM( CASE
        WHEN transactiontypecode = 'DEBIT' THEN
          amount
       END ) debit_amount,
  SUM( CASE
        WHEN transactiontypecode = 'CREDIT' THEN
          amount
       END ) credit_amount
FROM transactions
GROUP BY 
  TRUNC( date_created )
ORDER BY date_created;

DATE_CREA DEBIT_AMOUNT CREDIT_AMOUNT
--------- ------------ -------------
31-JUL-08       369.78         82.15

Really, not that bad. But if I want to do COUNT and AVG, I have to create more CASE statements like the ones above. My query will go from 14 lines to 30 in a hurry.

Here's the new cool way:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA DEBIT_TOTAL_AMOUNT CREDIT_TOTAL_AMOUNT
--------- ------------------ -------------------
31-JUL-08             369.78               82.15

It happens that is 14 rows as well. Now I'll add AVG and COUNT:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount,
  COUNT( amount ) total_count,
  AVG( amount ) avg_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA      D_AMT      D_CNT      D_AVG      C_AMT      C_CNT      C_AVG
--------- ---------- ---------- ---------- ---------- ---------- ----------
31-JUL-08     369.78          5     73.956      82.15          2     41.075

Nice! Sixteen lines of SQL...not bad at all. That should make code a bit more readable (it'll fit on a single page)...I like it!

Labels: 11g, development, sql

  ¶ 7/31/2008 05:15:00 PM 0 Comments Links to this post

  11g New Feature: Virtual Columns Not really so new as it's more than a year old...but I've finally had the opportunity to check it out. Well, opportunity at my last job which ended on Monday, but since I have so much free time now, I figured I give it a run.

Virtual Columns

Defined in the New Features doc as:

Virtual columns are defined by evaluating an expression the results of which become the metadata of the columns for tables. Virtual columns can be defined at table creation or modification time.

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

So what's it good for?

One thing that I see is that any derived column or expression can be placed at the table level (and indexed too!). This as opposed to putting it in a view and taking the chance that the logic is repeated exactly in every location.

So here goes my example:

CREATE TABLE virtual_columns
(
  id NUMBER
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30),
  middle_name VARCHAR2(20),
  last_name VARCHAR2(40),
  full_name AS
  ( CASE
      WHEN middle_name IS NULL THEN
        first_name || ' ' || last_name
      WHEN middle_name IS NOT NULL THEN
        first_name || ' ' || middle_name || ' ' || last_name
    END ) VIRTUAL
);

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 1,
    'CHET',
    NULL,
    'JUSTICE' );

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 2,
    'MICKEY',
    'CARL',
    'MOUSE' );

CJUSTICE@>SELECT * FROM virtual_columns;

        ID FIRST_NAME MIDD LAST_NAME  FULL_NAME
---------- ---------- ---- ---------- ------------------------------
         1 CHET            JUSTICE    CHET JUSTICE
         2 MICKEY     CARL MOUSE      MICKEY CARL MOUSE

2 rows selected.

Very cool stuff indeed! One thing that might be an issue is that if someone does a SELECT * from a table without any filters. If the expression is complex (SQL --> PL/SQL context switching), it might slow it down. Of course I have no evidence to back this up yet...just pondering.

Labels: 11g, database, development, oracle

  ¶ 7/30/2008 07:08:00 PM 2 Comments Links to this post