oracle 11g行转列（转）

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

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

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：

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));

已选择18行。

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

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

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;

已选择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

已选择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

注意，虽然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>

</item>

- <item>

<column name="TABLESPACE_NAME">SYSTEM</column>

</item>

- <item>

<column name="TABLESPACE_NAME">UNDOTBS1</column>

</item>

- <item>

<column name="TABLESPACE_NAME">YANGTK</column>

</item>

</PivotSet>

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

最后简单解释一下，前面所有的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

yangtingkun 发表于:2007.09.06 10:50

posted @ 2009-12-01 13:56 祝雄锋阅读(2084) 评论(0) 编辑收藏举报

zhuxiongfeng

oracle 11g行转列（转）

公告