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…..))]
