Oracle11新特性——行列转换语句（二）

在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行。