【IT168 Oracle】在11g以前，行列转化是一个比较麻烦的事情。对于列转行，需要使用很多的聚集函数加上DECODE语句。11g推出的PIVOT语法可以轻松的解决列转行的问题。
先根据USER_OBJECTS建立一张测试表：
SQL> CREATE TABLE T AS SELECT * FROM DBA_SEGMENTS;
表已创建。
为了将列转换为行，10g及以前版本需要：
在11g中可以使用新增的语法PIVOT：SQL> SELECT 2 OWNER, 3 SUM(DECODE(SEGMENT_TYPE, 'TABLE', BYTES)) TABLE_SIZE, 4 SUM(DECODE(SEGMENT_TYPE, 'TABLE PARTITION', BYTES)) TABLE_PART_SIZE, 5 SUM(DECODE(SEGMENT_TYPE, 'INDEX', BYTES)) INDEX_SIZE, 6 SUM(DECODE(SEGMENT_TYPE, 'INDEX PARTITION', BYTES)) INDEX_PART_SIZE 7 FROM T 8 GROUP BY OWNER; OWNER TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE ------------------------------ ---------- --------------- ---------- --------------- WKSYS 2621440 5177344 MDSYS 11993088 131072 6881280 393216 YANGTK 1114112 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 8716288 165216256 7929856 WMSYS 2424832 3866624 已选择18行。
简单介绍一下语法。PIVOT操作后面是聚集函数，这里需要计算汇总的空间，因此选择了SUM。然后是设置根据SEGMENT_TYPE的不同的值，显示为不同的列。SQL> SELECT * 2 FROM (SELECT OWNER, SEGMENT_TYPE, BYTES FROM T) 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)); OWNER TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE ------------------------------ ---------- --------------- ---------- --------------- WKSYS 2621440 5177344 MDSYS 11993088 131072 6881280 393216 YANGTK 1114112 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 8716288 165216256 7929856 WMSYS 2424832 3866624 已选择18行。
下面看一下二者的执行计划：
SQL> SET AUTOT ON SQL> SELECT 2 OWNER, 3 SUM(DECODE(SEGMENT_TYPE, 'TABLE', BYTES)) TABLE_SIZE, 4 SUM(DECODE(SEGMENT_TYPE, 'TABLE PARTITION', BYTES)) TABLE_PART_SIZE, 5 SUM(DECODE(SEGMENT_TYPE, 'INDEX', BYTES)) INDEX_SIZE, 6 SUM(DECODE(SEGMENT_TYPE, 'INDEX PARTITION', BYTES)) INDEX_PART_SIZE 7 FROM T 8 GROUP BY OWNER; OWNER TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE ------------------------------ ---------- --------------- ---------- --------------- WKSYS 2621440 5177344 MDSYS 11993088 131072 6881280 393216 YANGTK 1114112 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 8716288 165216256 7929856 WMSYS 2424832 3866624 已选择18行。 执行计划 ---------------------------------------------------------- Plan hash value: 47235625 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7786 | 311K| 36 (6)| 00:00:01 | | 1 | HASH GROUP BY | | 7786 | 311K| 36 (6)| 00:00:01 | | 2 | TABLE ACCESS FULL| T | 7786 | 311K| 34 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 113 consistent gets 0 physical reads 0 redo size 1129 bytes sent via SQL*Net to client 388 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 18 rows processed SQL> SELECT * 2 FROM (SELECT OWNER, SEGMENT_TYPE, BYTES FROM T) 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)); OWNER TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE ------------------------------ ---------- --------------- ---------- --------------- WKSYS 2621440 5177344 MDSYS 11993088 131072 6881280 393216 YANGTK 1114112 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 8716288 165216256 7929856 WMSYS 2424832 3866624 已选择18行。 执行计划 ---------------------------------------------------------- Plan hash value: 3924414983 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7786 | 311K| 36 (6)| 00:00:01 | | 1 | HASH GROUP BY PIVOT| | 7786 | 311K| 36 (6)| 00:00:01 | | 2 | TABLE ACCESS FULL | T | 7786 | 311K| 34 (0)| 00:00:01 | ---------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 113 consistent gets 0 physical reads 0 redo size 1129 bytes sent via SQL*Net to client 388 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 18 rows processed
注意，虽然PIVOT没有GROUP BY语句，但是执行过程中隐含了一个GROUP BY操作，执行计划为HASH GROUP BY PIVOT。虽然执行计划多了一个PIVOT，但是从cost和统计信息看，两个SQL的几乎没有差别。
也许有人认为，这个PIVOT并没有带来多大的简化，而且语法也没有原来的直观。不要着急，刚才只是最简单的一种情况，如果情况复杂一些，需要汇总两个字段，且判断行转列的条件也为两个，重写上面两个SQL。
SQL> SELECT TABLESPACE_NAME, 2 SUM(CASE WHEN SEGMENT_TYPE = 'TABLE' AND OWNER = 'SYS' THEN BYTES END) S_T_BYTES, 3 SUM(CASE WHEN SEGMENT_TYPE = 'TABLE' AND OWNER = 'SYS' THEN BLOCKS END) S_T_BLOCKS, 4 SUM(CASE WHEN SEGMENT_TYPE = 'TABLE' AND OWNER = 'YANGTK' THEN BYTES END) S_Y_BYTES, 5 SUM(CASE WHEN SEGMENT_TYPE = 'TABLE' AND OWNER = 'YANGTK' THEN BLOCKS END) S_Y_BLOCKS, 6 SUM(CASE WHEN SEGMENT_TYPE = 'INDEX' AND OWNER = 'SYS' THEN BYTES END) I_T_BYTES, 7 SUM(CASE WHEN SEGMENT_TYPE = 'INDEX' AND OWNER = 'SYS' THEN BLOCKS END) I_T_BLOCKS, 8 SUM(CASE WHEN SEGMENT_TYPE = 'INDEX' AND OWNER = 'YANGTK' THEN BYTES END) I_Y_BYTES, 9 SUM(CASE WHEN SEGMENT_TYPE = 'INDEX' AND OWNER = 'YANGTK' THEN BLOCKS END) I_Y_BLOCKS 10 FROM T 11 GROUP BY TABLESPACE_NAME; TABLESPACE_NAME S_T_BYTES S_T_BLOCKS S_Y_BYTES S_Y_BLOCKS I_T_BYTES I_T_BLOCKS I_Y_BYTES I_Y_BLOCKS ---------------- --------- ---------- --------- ---------- --------- ---------- --------- ---------- SYSAUX 33095680 4040 37683200 4600 YANGTK 1114112 136 65536 8 UNDOTBS1 SYSTEM 514260992 62776 127533056 15568 SQL> SELECT * 2 FROM (SELECT TABLESPACE_NAME, SEGMENT_TYPE, OWNER, BYTES, BLOCKS FROM T) 3 PIVOT (SUM(BYTES) BYTES, SUM(BLOCKS) BLOCKS 4 FOR (SEGMENT_TYPE, OWNER) IN 5 ( 6 ('TABLE', 'SYS') S_T, 7 ('TABLE', 'YANGTK') Y_T, 8 ('INDEX', 'SYS') S_I, 9 ('INDEX', 'YANGTK') Y_I 10 )); TABLESPACE_NAME S_T_BYTES S_T_BLOCKS Y_T_BYTES Y_T_BLOCKS S_I_BYTES S_I_BLOCKS Y_I_BYTES Y_I_BLOCKS ---------------- --------- ---------- --------- ---------- --------- ---------- --------- ---------- SYSAUX 33095680 4040 37683200 4600 YANGTK 1114112 136 65536 8 UNDOTBS1 SYSTEM 514260992 62776 127533056 15568
这一次那个SQL更加简单就一目了然了。而且，如果使用PIVOT XML功能，这个操作的功能更强大，在查询之前甚至可以不用了解需要转换的行数以及对应名称。利用子查询功能或者ANY关键字就可以实现。
SQL> SELECT * 2 FROM (SELECT TABLESPACE_NAME, BYTES FROM T) 3 PIVOT XML (SUM(BYTES) FOR TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM T)); TABLESPACE_NAME_XML ---------------------------------------------------------------------------------------------------- <PivotSet><item><column name = "TABLESPACE_NAME">SYSAUX</column><column name = "SUM(BYTES)">61551411 em><column name = "TABLESPACE_NAME">SYSTEM</column><column name = "SUM(BYTES)">727121920</column></i me = "TABLESPACE_NAME">UNDOTBS1</column><column name = "SUM(BYTES)">20250624</column></item><item><c PACE_NAME">YANGTK</column><column name = "SUM(BYTES)">1179648</column></item></PivotSet>
上面就是利用子查询在完全不清楚TABLESPACE_NAME包含数据的情况下得到的结果。由于是XML格式，通过SQLPLUS看不是很清晰，下面使用ANY关键字将结果输出到XML文件中：
SQL> host type e:call.sql set feedback off set pages 0 set lines 30000 set trims on set trim on set ver off set heading off set time off set timing off set autot off set echo off set show off set term off spo &1 @@execute.sql spo off set feedback 6 set pages 100 set lines 100 set heading on SQL> host type e:execute.sql SELECT * FROM (SELECT TABLESPACE_NAME, BYTES FROM T) PIVOT XML (SUM(BYTES) FOR TABLESPACE_NAME IN (ANY)); SQL> @E:CALL.SQL E:TABLESPACE.XML
最后直接用IE打开：
- <PivotSet> - <item> <column name="TABLESPACE_NAME">SYSAUX</column> <column name="SUM(BYTES)">615514112</column> </item> - <item> <column name="TABLESPACE_NAME">SYSTEM</column> <column name="SUM(BYTES)">727121920</column> </item> - <item> <column name="TABLESPACE_NAME">UNDOTBS1</column> <column name="SUM(BYTES)">20250624</column> </item> - <item> <column name="TABLESPACE_NAME">YANGTK</column> <column name="SUM(BYTES)">1179648</column> </item> </PivotSet>
最后简单解释一下，前面所有的PIVOT查询都是针对子查询的，这是为了聚集函数对制定的列进行GROUP BY，直接对表进行PIVOT也是可以的，只不过得到的结果将没有聚集效果：
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM T WHERE OWNER = 'YANGTK'; OWNER SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------------------ ------------------ YANGTK SYS_C009495 INDEX YANGTK T_STANDBY TABLE YANGTK T1 TABLE YANGTK T_PIVOT TABLE SQL> SELECT OWNER, SEGMENT_NAME, TABLE_SIZE, TABLE_PART_SIZE FROM T 2 PIVOT (SUM(BYTES) FOR SEGMENT_TYPE IN 3 ('TABLE' TABLE_SIZE, 'TABLE PARTITION' TABLE_PART_SIZE)) 4 WHERE OWNER = 'YANGTK'; OWNER SEGMENT_NAME TABLE_SIZE TABLE_PART_SIZE ------------------------------ ------------------------------ ---------- --------------- YANGTK SYS_C009495 YANGTK T_STANDBY 65536 YANGTK T_PIVOT 65536 YANGTK T1 983040
