Oracle11新特性——行列轉換語句（二）

打算寫一系列的文章介紹11g的新特性和變化。

這篇文章介紹11g的列轉行語法。

Oracle11新特性——行列轉換語句（一）：http://yangtingkun.itpub.net/post/468/392770

在11g以前，行列轉化是一個比較麻煩的事情。對於行轉列來說，以前只能使用UNION ALL語句，顯得十分的麻煩，11g提供了UNPIVOT語句，可以很方便的解決這個問題。

先做一個測試表，利用上一篇介紹的PIVOT語句：

SQL> CREATE TABLE T_PIVOT AS SELECT *
2 FROM (SELECT OWNER, SEGMENT_TYPE, BYTES FROM DBA_SEGMENTS)
3 PIVOT (SUM(BYTES) FOR SEGMENT_TYPE IN
4 ('TABLE' TABLE_SIZE, 'TABLE PARTITION' TABLE_PART_SIZE,
5 'INDEX' INDEX_SIZE, 'INDEX PARTITION' INDEX_PART_SIZE));

表已建立。

SQL> SELECT * FROM T_PIVOT;

OWNER TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE
------------------------------ ---------- --------------- ---------- ---------------
WKSYS 2621440 5177344
MDSYS 11993088 131072 6881280 393216
YANGTK 2031616 65536
TSMSYS 65536 65536
WK_TEST 5767168 6553600
OUTLN 196608 262144
CTXSYS 2031616 3407872
OLAPSYS 7471104 8847360
FLOWS_FILES 65536 262144
SYSTEM 7077888 2490368 9568256 3276800
EXFSYS 1310720 2490368
DBSNMP 1048576 524288
ORDSYS 3670016 5832704
SYSMAN 53608448 69402624
XDB 17170432 7471104
FLOWS_030000 41680896 54329344
SYS 547356672 9043968 165216256 8388608
WMSYS 2424832 3866624

已選擇18行。

在10g及以前版本要實現行轉列：

SQL> SELECT OWNER, 'TABLE' OBJECT_TYPE, TABLE_SIZE BYTES FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK')
2 UNION ALL
3 SELECT OWNER, 'TABLE PARTITION', TABLE_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK')
4 UNION ALL
5 SELECT OWNER, 'INDEX', INDEX_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK')
6 UNION ALL
7 SELECT OWNER, 'INDEX PARTITION', INDEX_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK');

OWNER OBJECT_TYPE BYTES
------------------------------ --------------- ----------
YANGTK TABLE 2031616
SYS TABLE 547356672
YANGTK TABLE PARTITION
SYS TABLE PARTITION 9043968
YANGTK INDEX 65536
SYS INDEX 165216256
YANGTK INDEX PARTITION
SYS INDEX PARTITION 8388608

已選擇8行。

這種方法相對來說比較麻煩，用UNPIVOT則會簡化很多：

SQL> SELECT OWNER, OBJECT_TYPE, BYTES FROM T_PIVOT
2 UNPIVOT INCLUDE NULLS (BYTES FOR OBJECT_TYPE IN
3 (TABLE_SIZE AS 'TABLE', TABLE_PART_SIZE AS 'TABLE PARTITION',
4 INDEX_SIZE AS 'INDEX', INDEX_PART_SIZE AS 'INDEX PARTITION'))
5 WHERE OWNER IN ('SYS', 'YANGTK');

OWNER OBJECT_TYPE BYTES
------------------------------ --------------- ----------
YANGTK TABLE 2031616
YANGTK TABLE PARTITION
YANGTK INDEX 65536
YANGTK INDEX PARTITION
SYS TABLE 547356672
SYS TABLE PARTITION 9043968
SYS INDEX 165216256
SYS INDEX PARTITION 8388608

已選擇8行。

不光是語法上的簡化，從執行計劃和統計資訊上看：

SQL> SET AUTOT TRACE
SQL> SELECT OWNER, 'TABLE' OBJECT_TYPE, TABLE_SIZE BYTES FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK')
2 UNION ALL
3 SELECT OWNER, 'TABLE PARTITION', TABLE_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK')
4 UNION ALL
5 SELECT OWNER, 'INDEX', INDEX_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK')
6 UNION ALL
7 SELECT OWNER, 'INDEX PARTITION', INDEX_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK');

已選擇8行。

執行計劃
----------------------------------------------------------
Plan hash value: 634273332

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 240 | 12 (75)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS' OR "OWNER"='YANGTK')
3 - filter("OWNER"='SYS' OR "OWNER"='YANGTK')
4 - filter("OWNER"='SYS' OR "OWNER"='YANGTK')
5 - filter("OWNER"='SYS' OR "OWNER"='YANGTK')

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed

SQL> SELECT OWNER, OBJECT_TYPE, BYTES FROM T_PIVOT
2 UNPIVOT INCLUDE NULLS (BYTES FOR OBJECT_TYPE IN
3 (TABLE_SIZE AS 'TABLE', TABLE_PART_SIZE AS 'TABLE PARTITION',
4 INDEX_SIZE AS 'INDEX', INDEX_PART_SIZE AS 'INDEX PARTITION'))
5 WHERE OWNER IN ('SYS', 'YANGTK');

已選擇8行。

執行計劃
----------------------------------------------------------
Plan hash value: 2063660069

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 273 | 12 (0)| 00:00:01 |
| 1 | VIEW | | 7 | 273 | 12 (0)| 00:00:01 |
| 2 | UNPIVOT | | | | | |
|* 3 | TABLE ACCESS FULL| T_PIVOT | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T_PIVOT"."OWNER"='SYS' OR "T_PIVOT"."OWNER"='YANGTK')

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
631 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed

如果不使用UNPIVOT，那麼需要多個UNION ALL，多次全表掃描。而對於UNPIVOT操作，只需要一個全表掃描就可以了。

對於UNPIVOT還可以不選擇為NULL的結果，將上面的INCLUDE NULLS去掉就可以了：

SQL> SET AUTOT OFF
SQL> SELECT OWNER, OBJECT_TYPE, BYTES FROM T_PIVOT
2 UNPIVOT (BYTES FOR OBJECT_TYPE IN
3 (TABLE_SIZE AS 'TABLE', TABLE_PART_SIZE AS 'TABLE PARTITION',
4 INDEX_SIZE AS 'INDEX', INDEX_PART_SIZE AS 'INDEX PARTITION'))
5 WHERE OWNER IN ('SYS', 'YANGTK');

OWNER OBJECT_TYPE BYTES
------------------------------ --------------- ----------
YANGTK TABLE 2031616
YANGTK INDEX 65536
SYS TABLE 547356672
SYS TABLE PARTITION 9043968
SYS INDEX 165216256
SYS INDEX PARTITION 8388608

已選擇6行。

如果不指定列對應的常量，那麼會直接將列名作為分類的名稱：

SQL> SELECT * FROM T_PIVOT
2 UNPIVOT (BYTES FOR OBJECT_TYPE IN
3 (TABLE_SIZE, TABLE_PART_SIZE, INDEX_SIZE, INDEX_PART_SIZE))
4 WHERE OWNER IN ('SYS', 'YANGTK');

OWNER OBJECT_TYPE BYTES
------------------------------ --------------- ----------
YANGTK TABLE_SIZE 2031616
YANGTK INDEX_SIZE 65536
SYS TABLE_SIZE 547356672
SYS TABLE_PART_SIZE 9043968
SYS INDEX_SIZE 165216256
SYS INDEX_PART_SIZE 8388608

已選擇6行。