Oracle11新特性——行列转换语句

 

原文出自：http://yangtingkun.itpub.net/post/468/392770

在11g以前，行列转化是一个比较麻烦的事情。对于列转行，需要使用很多的聚集函数加上DECODE语句。11g推出的PIVOT语法可以轻松的解决列转行的问题。

先根据USER_OBJECTS建立一张测试表：

PHP code:



SQL> CREATE TABLE T AS SELECT * FROM DBA_SEGMENTS;





表已创建。





为了将列转换为行，10g及以前版本需要：





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





.

在11g中可以使用新增的语法PIVOT：

PHP code:



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





.

简单介绍一下语法。PIVOT操作后面是聚集函数，这里需要计算汇总的空间，因此选择了SUM。然后是设置根据SEGMENT_TYPE的不同的值，显示为不同的列。

下面看一下二者的执行计划：

PHP code:



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。

PHP code:



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关键字就可以实现。

PHP code:



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 = &quot;TABLESPACE_NAME&quot;>SYSAUXcolumn><column name = &quot;SUM(BYTES)&quot;>61551411


em><column name = &quot;TABLESPACE_NAME&quot;>SYSTEMcolumn><column name = &quot;SUM(BYTES)&quot;>727121920column>i


me = &quot;TABLESPACE_NAME&quot;>UNDOTBS1column><column name = &quot;SUM(BYTES)&quot;>20250624column>item><item><c


PACE_NAME&quot;>YANGTKcolumn><column name = &quot;SUM(BYTES)&quot;>1179648column>item>PivotSet>





.

上面就是利用子查询在完全不清楚TABLESPACE_NAME包含数据的情况下得到的结果。由于是XML格式，通过SQLPLUS看不是很清晰，下面使用ANY关键字将结果输出到XML文件中：

PHP code:



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打开：

PHP code:



- <PivotSet>


- <item>


  <column name=&quot;TABLESPACE_NAME&quot;>SYSAUXcolumn> 


  <column name=&quot;SUM(BYTES)&quot;>615514112column> 


  item>


- <item>


  <column name=&quot;TABLESPACE_NAME&quot;>SYSTEMcolumn> 


  <column name=&quot;SUM(BYTES)&quot;>727121920column> 


  item>


- <item>


  <column name=&quot;TABLESPACE_NAME&quot;>UNDOTBS1column> 


  <column name=&quot;SUM(BYTES)&quot;>20250624column> 


  item>


- <item>


  <column name=&quot;TABLESPACE_NAME&quot;>YANGTKcolumn> 


  <column name=&quot;SUM(BYTES)&quot;>1179648column> 


  item>


  PivotSet>





.

关于CALL.SQL的设置，可以参考http://yangtingkun.itpub.net/post/468/31622

最后简单解释一下，前面所有的PIVOT查询都是针对子查询的，这是为了聚集函数对制定的列进行GROUP BY，直接对表进行PIVOT也是可以的，只不过得到的结果将没有聚集效果：

PHP code:



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





.