Vishal desai’s Oracle Blog

October 27, 2010

11g pivot unpivot and Database Design

Filed under: Design, SQL — vishaldesai @ 5:59 pm

Oracle 11g pivot and unpivot function can help if database tables are not in second normal form. I will try to unveil this using a simple example:

Table Structures

CREATE TABLE emp1
  (
    empno   NUMBER PRIMARY KEY,
    empname VARCHAR2(1000),
    job     VARCHAR2(100),
    hiredate DATE
  );

CREATE TABLE sal1
  (
    empno   NUMBER,
    sal2000 NUMBER,
    sal2001 NUMBER,
    sal2002 NUMBER,
    CONSTRAINT fk_sal1_emp1 FOREIGN KEY (empno) REFERENCES emp1(empno)
  );

--Inserted few records into both emp1 and sal1 table.

We have seen table structures like sal1 (sal2000,sal2001,sal2002 …….). If we have to write a salary report as shown below we have to join SAL1 table multiple times. I used union but there are different ways to write similar query by joining SAL1 multiple times.

10g SQL

SELECT a.empno,
  a.empname,
  a.job,
  'sal2000' AS SALYEAR ,
  b1.sal2000 sal2000to2002
FROM emp1 a,
  sal1 b1
WHERE a.empno=b1.empno
UNION
SELECT a.empno,
  a.empname,
  a.job,
  'sal2001' AS SALYEAR ,
  b1.sal2001 sal2000to2002
FROM emp1 a,
  sal1 b1
WHERE a.empno=b1.empno
UNION
SELECT a.empno,
  a.empname,
  a.job,
  'sal2002' AS SALYEAR ,
  b1.sal2002 sal2000to2002
FROM emp1 a,
  SAL1 B1
WHERE a.empno=b1.empno;

    EMPNO EMPNAME                        JOB                            SALYEAR SAL2000TO2002
---------- ------------------------------ ------------------------------ ------- -------------
      7369 SMITH                          CLERK                          sal2000            10
      7369 SMITH                          CLERK                          sal2001            11
      7369 SMITH                          CLERK                          sal2002            12
      7499 ALLEN                          SALESMAN                       sal2000            20
      7499 ALLEN                          SALESMAN                       sal2001            21
      7499 ALLEN                          SALESMAN                       sal2002            22
      7521 WARD                           SALESMAN                       sal2000            21
      7521 WARD                           SALESMAN                       sal2001            22
      7521 WARD                           SALESMAN                       sal2002            23
      7654 MARTIN                         SALESMAN                       sal2000            23
      7654 MARTIN                         SALESMAN                       sal2001            24
      7654 MARTIN                         SALESMAN                       sal2002            25
      7698 BLAKE                          MANAGER                        sal2000            31
      7698 BLAKE                          MANAGER                        sal2001            32
      7698 BLAKE                          MANAGER                        sal2002            33
      7782 CLARK                          MANAGER                        sal2000            31
      7782 CLARK                          MANAGER                        sal2001            32
      7782 CLARK                          MANAGER                        sal2002            33
      7788 SCOTT                          ANALYST                        sal2000            25
      7788 SCOTT                          ANALYST                        sal2001            25
      7788 SCOTT                          ANALYST                        sal2002            25
      7839 KING                           PRESIDENT                      sal2000           100
      7839 KING                           PRESIDENT                      sal2001           110
      7839 KING                           PRESIDENT                      sal2002           120
      7844 TURNER                         SALESMAN                       sal2000            10
      7844 TURNER                         SALESMAN                       sal2001            11
      7844 TURNER                         SALESMAN                       sal2002            12
      7876 ADAMS                          CLERK                          sal2000            10
      7876 ADAMS                          CLERK                          sal2001            11
      7876 ADAMS                          CLERK                          sal2002            12
      7900 JAMES                          CLERK                          sal2000            17
      7900 JAMES                          CLERK                          sal2001            18
      7900 JAMES                          CLERK                          sal2002            19
      7902 FORD                           ANALYST                        sal2000            25
      7902 FORD                           ANALYST                        sal2001            26
      7902 FORD                           ANALYST                        sal2002            27
      7934 MILLER                         CLERK                          sal2000            10
      7934 MILLER                         CLERK                          sal2001            11
      7934 MILLER                         CLERK                          sal2002            12

39 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18 Card=39 Bytes=23361)
   1    0   SORT (UNIQUE) (Cost=18 Card=39 Bytes=23361)
   2    1     UNION-ALL
   3    2       HASH JOIN (Cost=7 Card=13 Bytes=7709)
   4    3         TABLE ACCESS (FULL) OF 'SAL1' (TABLE) (Cost=3 Card=13 Bytes=338)
   5    3         TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=7938)
   6    2       HASH JOIN (Cost=10 Card=26 Bytes=15652)
   7    6         TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=7938)
   8    6         VIEW OF 'VW_JF_SET$7FC4F042' (VIEW) (Cost=6 Card=26 Bytes=910)
   9    8           SORT (UNIQUE) (Cost=6 Card=26 Bytes=676)
  10    9             UNION-ALL
  11   10               TABLE ACCESS (FULL) OF 'SAL1' (TABLE) (Cost=3  Card=13 Bytes=338)
  12   10               TABLE ACCESS (FULL) OF 'SAL1' (TABLE) (Cost=3  Card=13 Bytes=338)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
       1563  bytes sent via SQL*Net to client
        457  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         39  rows processed

As you can see from query and execution plan we scanned table SAL1 three times. If we have to get report for 10 years we will have to scan SAL1 for ten times and so on.

11g SQL

SELECT a.empno,
  a.empname,
  a.job,
  c.property SALYEAR,
  c.value SAL2000TO2002
FROM emp1 a,
  (SELECT empno,
    property,
    value
  FROM sal1 unpivot exclude nulls (value FOR property IN (sal2000,sal2001,sal2002))
  ) c
WHERE a.empno=c.empno;

      EMPNO EMPNAME                        JOB                            SALYEAR SAL2000TO2002
---------- ------------------------------ ------------------------------ ------- -------------
      7369 SMITH                          CLERK                          SAL2000            10
      7369 SMITH                          CLERK                          SAL2001            11
      7369 SMITH                          CLERK                          SAL2002            12
      7499 ALLEN                          SALESMAN                       SAL2000            20
      7499 ALLEN                          SALESMAN                       SAL2001            21
      7499 ALLEN                          SALESMAN                       SAL2002            22
      7521 WARD                           SALESMAN                       SAL2000            21
      7521 WARD                           SALESMAN                       SAL2001            22
      7521 WARD                           SALESMAN                       SAL2002            23
      7654 MARTIN                         SALESMAN                       SAL2000            23
      7654 MARTIN                         SALESMAN                       SAL2001            24
      7654 MARTIN                         SALESMAN                       SAL2002            25
      7698 BLAKE                          MANAGER                        SAL2000            31
      7698 BLAKE                          MANAGER                        SAL2001            32
      7698 BLAKE                          MANAGER                        SAL2002            33
      7782 CLARK                          MANAGER                        SAL2000            31
      7782 CLARK                          MANAGER                        SAL2001            32
      7782 CLARK                          MANAGER                        SAL2002            33
      7788 SCOTT                          ANALYST                        SAL2000            25
      7788 SCOTT                          ANALYST                        SAL2001            25
      7788 SCOTT                          ANALYST                        SAL2002            25
      7839 KING                           PRESIDENT                      SAL2000           100
      7839 KING                           PRESIDENT                      SAL2001           110
      7839 KING                           PRESIDENT                      SAL2002           120
      7844 TURNER                         SALESMAN                       SAL2000            10
      7844 TURNER                         SALESMAN                       SAL2001            11
      7844 TURNER                         SALESMAN                       SAL2002            12
      7876 ADAMS                          CLERK                          SAL2000            10
      7876 ADAMS                          CLERK                          SAL2001            11
      7876 ADAMS                          CLERK                          SAL2002            12
      7900 JAMES                          CLERK                          SAL2000            17
      7900 JAMES                          CLERK                          SAL2001            18
      7900 JAMES                          CLERK                          SAL2002            19
      7902 FORD                           ANALYST                        SAL2000            25
      7902 FORD                           ANALYST                        SAL2001            26
      7902 FORD                           ANALYST                        SAL2002            27
      7934 MILLER                         CLERK                          SAL2000            10
      7934 MILLER                         CLERK                          SAL2001            11
      7934 MILLER                         CLERK                          SAL2002            12

39 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=39 Bytes=23478)
   1    0   HASH JOIN (Cost=13 Card=39 Bytes=23478)
   2    1     TABLE ACCESS (FULL) OF 'EMP1' (TABLE) (Cost=3 Card=14 Bytes=7938)
   3    1     VIEW (Cost=9 Card=39 Bytes=1365)
   4    3       UNPIVOT
   5    4         TABLE ACCESS (FULL) OF 'SAL1' (TABLE) (Cost=3 Card=13 Bytes=676)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       1654  bytes sent via SQL*Net to client
        457  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         39  rows processed

With unpivot function we scanned SAL1 only once and that means less resource utilization and better performance. So can we just forget about design? No as we have to maintain code every time we add another column to table. For eg we have to change code if we add sal2003, sal2004 column and so on i.e. [FROM sal1 unpivot exclude nulls (value FOR property IN (sal2000,sal2001,sal2002,sal2003,sal2004…..))]

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.