幾個行列轉換的實用小例子

說起行列轉換，大家是既熟悉又陌生，在oracle 10g版本之前如果要做行列轉換，都基本得使用decode來完成，在11g中情況有了改觀，可以直接使用pivot特性來完成。這種方式相比decode來說要更加簡潔清晰。
我們來舉兩個例子來說明一下。
--&gtsession狀態的監控
對於資料庫中的session狀態監控可以作為系統運維工作的一部分，一旦發現session異常就可以很快定位出可能是哪些型別的問題。
我們建立一個臨時表來替代v$session來說明。
create table session_test as select *from v$session;

select username,count(*)cnt,status from session_test group by username,status;

我們得到的當前session情況如下：
USERNAME                              CNT STATUS
------------------------------ ---------- --------
N1                                      1 ACTIVE
MIG                                     1 INACTIVE
                                       25 ACTIVE
如果使用者很多，同一個使用者下存在多個session(ACTIVE或者INACTIVE)。
檢視的時候就不是很清晰。如果能夠行列轉換一下，顯示成下面的格式就好了。
username    active  inactive sniped.....
xxxx            1        2           0
xxxx             2       0           0
可以使用pivot來實現。

select *from
(
select username,status from session_test
)
pivot
(
count(status)
for status in ('ACTIVE','INACTIVE','KILLED','SNIPED','CACHED')
)
order by username nulls first;
輸出結果如下：

USERNAME                         'ACTIVE' 'INACTIVE'   'KILLED'   'SNIPED'   'CACHED'
------------------------------ ---------- ---------- ---------- ---------- ----------
                                       25          0          0          0          0
MIG                                     0          1          0          0          0
N1                                      1          0          0          0          0
這種方式相比以前的decode方式要好很多，而且要清晰。不過有一點，這種方式中，for status in ('ACTIVE','INACTIVE','KILLED','SNIPED','CACHED')的部分是需要預先知道的。在這個例子中不是問題，因為狀態很有限，就那幾個，可以透過官方文件中的v$session欄位解釋得到。session的狀態就下面5種。

STATUS

VARCHAR2(8)

Status of the session: ACTIVE - Session currently executing SQL INACTIVE KILLED - Session marked to be killed CACHED - Session temporarily cached for use by Oracle*XA SNIPED - Session inactive, waiting on the client

 
--&gt檢視schema下的object_type
如果我們檢視一個資料庫中資料的整體分佈情況，想知道每個schema下的object分佈情況，比如存在多少個表，多少索引，同義詞等，這些對於很多潛在的問題分析都是很有幫助的。如果同樣結構的環境中丟失了某一個同義詞，可能需要很長的時間才能發現問題，如果我們簡單比較一下object的分佈情況，就可以很容易的分析出問題來。
我們採用all_objects做為資料來源,建立一個臨時表來處理。

create table obj_test as select *from all_objects;

select owner,object_type,count(*) from obj_test group by owner,object_type order by owner;
得到的結果如下：

SCOTT                          TABLE                        5
SYS                            CLUSTER                     10
SYS                            CONSUMER GROUP               2
SYS                            CONTEXT                      3
SYS                            DESTINATION                  2
SYS                            DIRECTORY                   10
SYS                            EDITION                      1
SYS                            EVALUATION CONTEXT          10

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
SYS                            FUNCTION                    87
SYS                            INDEX                     1018
SYS                            INDEX PARTITION            121
SYS                            JOB                          9
SYS                            JOB CLASS                   13
SYS                            LIBRARY                    142
SYS                            OPERATOR                     7
SYS                            PACKAGE                    579
SYS                            PACKAGE BODY               556
SYS                            PROCEDURE                   93
SYS                            PROGRAM                     19
SYS                            RULE                         1
SYS                            RULE SET                    13
SYS                            SCHEDULE                     3
SYS                            SCHEDULER GROUP              4
SYS                            SEQUENCE                   116
SYS                            SYNONYM                      6
SYS                            TABLE                      989
SYS                            TABLE PARTITION            121
SYS                            TABLE SUBPARTITION          32
SYS                            TRIGGER                      4

可以很清楚地看到每個使用者下的object的分佈情況，但是這樣看很不清晰，而且我們可能並不需要知道所有的object_type,我們只是想得到一些基本的object type的情況。
可以採用如下的方式來做。
select *from
(
select owner,object_type from obj_test
)
pivot
(
count(*)
for object_type in ('TABLE','INDEX','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','VIEW')
)
order by owner;
OWNER                             'TABLE'    'INDEX'  'SYNONYM' 'PROCEDURE' 'FUNCTION'  'PACKAGE'     'VIEW'
------------------------------ ---------- ---------- ---------- ----------- ---------- ---------- ----------
APPQOSSYS                               4          0          1           0          0          0          0
APP_CONN                                0          0          4           0          0          0          0
DBSNMP                                 20         10          1           1          0          3          7
JEANRON                                 1          0          0           0          0          0          0
MIG                                     0          0          1           0          0          0          0
N1                                     46         10          0           5          3          3          0
ORACLE_OCM                              0          0          0           0          0          3          0
OUTLN                                   3          4          0           1          0          0          0
PUBLIC                                  0          0       3281           0          0          0          0
SCOTT                                   5          2          0           0          0          0          0
SYS                                   989       1018          6          93         87        579       3730
SYSTEM                                157        215          8           1          0          1         14
TABOWNER                                1          0          0           0          0          0          0
上面這種方式要清晰地多，而且更有針對性。

當然關於行列轉換，model子句也是很實用的，準備在後續的部落格中繼續分享。我們拋磚引玉，用一個在論壇中比較有意思的例子來說明一下。
大家能夠猜出下面的sql語句執行的結果嗎？稍微停頓一下再看答案。
select c from
(select * from
(select 'oracle' cc, level no from dual connect by level <= length('oracle'))
model return updated rows
dimension by (no)
measures (cc c, no n)
rules (
   c[any] = substr(c[cv()],n[cv()],1)
));

C
------
o
r
a
c
l
e

6 rows selected.