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

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

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> 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
----------------------------------------------------------------------------------------------------
SYSAUX61551411
em>SYSTEM727121920me = "TABLESPACE_NAME">UNDOTBS120250624PACE_NAME">YANGTK1179648

上面就是利用子查詢在完全不清楚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">SYSAUXcolumn>

<column name="SUM(BYTES)">615514112column>

item>

- <item>

<column name="TABLESPACE_NAME">SYSTEMcolumn>

<column name="SUM(BYTES)">727121920column>

item>

- <item>

<column name="TABLESPACE_NAME">UNDOTBS1column>

<column name="SUM(BYTES)">20250624column>

item>

- <item>

<column name="TABLESPACE_NAME">YANGTKcolumn>

<column name="SUM(BYTES)">1179648column>

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