利用Oracle的新特性优化SQL

今天同事发给我一个SQL，目的是得到一个报表，现在的问题是，这个SQL执行时间太长，基本上不可以接受。

这个SQL如下：

SELECT
_;WiRRQ0 P.ID,ITPUB个人空间7SA| S.Qcq.R
 COUNT(DISTINCT H.PRODUCT_ID) PRODUCT_CNT,ITPUB个人空间)Va,oGn8v/P~;m4a
 COUNT(DISTINCT H.BUYER_ORGID) BUYER_CNT,
$sy%`"k j t#{sl0k1S0 COUNT(DISTINCT H.SENDER_ORGID) SENDER_CNT,
^f1O X,E qZ7H*mx0 COUNT(DISTINCT DECODE(H.SOURCE_TYPE, '1', H.PRODUCT_ID, NULL)) PROD1_CNT,
vh ]!HR-VjW0 COUNT(DISTINCT DECODE(H.SOURCE_TYPE, '2', H.PRODUCT_ID, NULL)) PROD2_CNT,ITPUB个人空间@(WL2C+I1fl
 COUNT(DISTINCT DECODE(H.SOURCE_TYPE, '5', H.PRODUCT_ID, NULL)) PROD5_CNT
F-t"gq5R@/`+p0FROM ORD_HIT_COMM H,PLT_PLAT P
4}6Kkt I0WHERE H.ENABLE_FLAG = '1'
m} Li;B0AND H.PLAT_ID IN
t7hF bUEaK0 (
S'],\8N/_l)U/n1sZ9n0  SELECT P2.ID
$hLF8m s7QWgm0  FROM PLT_PLAT P2ITPUB个人空间 K`!nh%T4Hd
  START WITH P2.ID = P.ID
jvO'wBP%O%^5x9Y0  CONNECT BY PRIOR P2.ID = P2.PLAT_FATHER
3Q)Z\:f8t0 )ITPUB个人空间/NT R4i#Sa?'G I
GROUP BY P.ID;

SQL并不长，逻辑也不复杂，但是执行时间比较恐怖，在SQL执行了三个多小时后，将其中止，中止前查询V$SESSION_LONGOPS，发现查询时间居然需要一天以上：

SQL> SELECT SID, TARGET, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDSITPUB个人空间bS5A%\bf&U
  2  FROM V$SESSION_LONGOPS 
I+M aj B3nG0  3  WHERE SID = 57
xt9B]U0  4  AND TOTALWORK != SOFAR;

       SID TARGET                    SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
BneRd0---------- -------------------- ---------- ---------- -------------- ---------------ITPUB个人空间-^DqJT$]-k}:^$n)f
        57 JIANGSU.ORD_HIT_COMM      14236      89780          56133           10578

SQL中的ORD_HIT_COMM是一个事实表，其中包括大量的数据，表中的总记录数为：

SQL> SELECT COUNT(*) FROM ORD_HIT_COMM;

  COUNT(*)
;`,q$}!K9It2|#_0----------
d,Y;R+i.ti0   1712519

SQL中的PLT_PLAT是一个小的维度表，表中记录分为4级，第0级包括一条记录，是所有记录的根；第1级记录是0级记录的子节点；第2级和第3级分别是第1级和第2级的子记录，整个表中的信息树型连接的。

SQL> SELECT PLAT_GRADE, COUNT(*) FROM PLT_PLAT
g,vyP H*F/z0  2  GROUP BY ROLLUP(PLAT_GRADE);

PLAT_GRADE   COUNT(*)
#p:aDI'n,`8tj*[ ~0---------- ----------
RF n5l2kW8}0         0          1ITPUB个人空间Djdc}G
s^Z:Z
         1          4ITPUB个人空间+@\ W$VD:Mhh#_2kB
         2         23ITPUB个人空间.mm$|7v8x8[;u7W
         3          5ITPUB个人空间+Sl9GG \(t:|.C`2~
                   33

而表ORD_HIT_COMM的记录对应表中的1或2级上，这个SQL的目的是根据树形的关系，分别统计各个PLAT_ID上对应的事实表中维度的计数。

检查SQL的执行计划：

SQL> EXPLAIN PLAN FOR
D/S$p^d%D"j0  2  SELECT
R[(q;g2s-wek5Nd0  3   P.ID,ITPUB个人空间!h9Y2|(Z1`{ F~
  4   COUNT(DISTINCT H.PRODUCT_ID) PRODUCT_CNT,
Nv$k,kB9f e0  5   COUNT(DISTINCT H.BUYER_ORGID) BUYER_CNT,
]'u-G'M5t%p1p|.{|0  6   COUNT(DISTINCT H.SENDER_ORGID) SENDER_CNT,
s/cK8Ax m w0  7   COUNT(DISTINCT DECODE(H.SOURCE_TYPE, '1', H.PRODUCT_ID, NULL)) PROD1_CNT,
E9o~ASO0o,|p;P(["v0c k0  8   COUNT(DISTINCT DECODE(H.SOURCE_TYPE, '2', H.PRODUCT_ID, NULL)) PROD2_CNT,ITPUB个人空间J0lm!rF%l4wlM
  9   COUNT(DISTINCT DECODE(H.SOURCE_TYPE, '5', H.PRODUCT_ID, NULL)) PROD5_CNT
.o-vy*u4w*y0 10  FROM ORD_HIT_COMM H,PLT_PLAT P
NYud'Z8} X0 11  WHERE H.ENABLE_FLAG = '1'
2E6K*[@0Okc{0 12  AND H.PLAT_ID IN
L]2?j ` B/W5|p2{0 13   (
!r l4JB;vm0 14    SELECT P2.IDITPUB个人空间!QJ*a+R
b4S
 15    FROM PLT_PLAT P2
5Mb8h3K%^r0 16    START WITH P2.ID = P.ID
DP}*s'k{(w0 17    CONNECT BY PRIOR P2.ID = P2.PLAT_FATHERITPUB个人空间.aQ:c+xk
 18   )ITPUB个人空间:q2Gb w(N?EL
 19  GROUP BY P.ID;

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUTITPUB个人空间9q'e6`o'gZ;f7k
--------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------ITPUB个人空间]7WNpfq
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)|ITPUB个人空间[xP _z*{5XP Q3X
-------------------------------------------------------------------------------------
l'D{-gT0|   0 | SELECT STATEMENT                |              |    33 |  4257 |   574K  (2)|ITPUB个人空间g7c1dK3cFh
|   1 |  SORT GROUP BY                  |              |    33 |  4257 |   574K  (2)|
#Cc@ s$E0|   2 |   FILTER                        |              |       |       |            |ITPUB个人空间\j&zN
Y
|   3 |    MERGE JOIN CARTESIAN         |              |   53M| 6586M|   567K  (1)|ITPUB个人空间A1hhqH"L
|   4 |     TABLE ACCESS FULL           | ORD_HIT_COMM |  1622K|  160M| 29868   (1)|ITPUB个人空间!|zb1|T,s)t!v
|   5 |     BUFFER SORT                 |              |    33 |   825 |   543K  (2)|
?9YNT;P/GS+}0|   6 |      INDEX FAST FULL SCAN       | PK_PLT_PLAT  |    33 |   825 |     0   (0)|ITPUB个人空间Z'ksRg y9_iS
|   7 |    FILTER                       |              |       |       |            |ITPUB个人空间5ky8t(c.QBp7X0_
|   8 |     CONNECT BY WITH FILTERING   |              |       |       |            |
I3ITI.@0|   9 |      TABLE ACCESS BY INDEX ROWID| PLT_PLAT     |       |       |            |ITPUB个人空间(B
m#\|f%K
|  10 |       INDEX UNIQUE SCAN         | PK_PLT_PLAT  |     1 |    25 |     0   (0)|ITPUB个人空间BB`H;`d
|  11 |      HASH JOIN                  |              |       |       |            |
X+xp0B DP/g&c0|  12 |       CONNECT BY PUMP           |              |       |       |            |ITPUB个人空间d[l4\F9Y"d!L
|  13 |       TABLE ACCESS FULL         | PLT_PLAT     |    33 |  1650 |     2   (0)|ITPUB个人空间z.b8g(u3n!N+[ S
|  14 |      TABLE ACCESS FULL          | PLT_PLAT     |    33 |  1650 |     2   (0)|
+D(S&@;}e@aRw s?0-------------------------------------------------------------------------------------

已选择24行。

执行计划包括了MERGE JOIN CARTESIAN，而且其中一个表是百万级，另一个表虽然记录并不多，但是为了构造树形查询，得多所有结果的遍历，结果集也达到了20W：

SQL> SELECT COUNT(*)
/~3J8b@-s0L?-\1U0  2  FROM
"~%xZk4o[y0  3  ( SELECT P2.IDITPUB个人空间5a t2Ig'lJ(u\
  4   FROM PLT_PLAT P2, PLT_PLAT PITPUB个人空间eX"v'Z)C.o)Pup
  5   START WITH P2.ID = P.ID
K6D4TL(hr\J%}0  6   CONNECT BY PRIOR P2.ID = P2.PLAT_FATHER
O,F+g/bb(GG$i`g0  7  )ITPUB个人空间.OW2h2muN#B]$ZR6?
  8  ;

  COUNT(*)ITPUB个人空间8SI~u_kA3ZN DH
----------ITPUB个人空间3z,@E+J9b\2x
    211266

一个百万记录的表和一个二十万记录的表进行笛卡儿积，结果可想而知。

其实，这里的PLT_PLAT自关联的树形查询无非是为了获取每个ID包括的所有叶节点，如果这里转换一下思路，在通过树形查询遍历整个表的时候，通过10g新特性提供的CONNECT_BY_ROOT来获取当前记录的根节点，那么只需要一次遍历，就可以得到PLAT_ID的列表，这个结果和事实表去关联，并根据根节点分组，就可以满足结果：

SQL> SELECT RID ID,
x-e)X;\esbY0  2   COUNT(DISTINCT PRODUCT_ID) PROD_CNT,
F#Y+Pd D
^ ]"W/fKF0  3   COUNT(DISTINCT BUYER_ORGID) BUYER_CNT,
y9B\ dp o&_H4u*m;q0  4   COUNT(DISTINCT SENDER_ORGID) SENDER_CNT,
0ASh:Z%@Ayn8I
Z0  5   COUNT(DISTINCT PROD1) PROD1_CNT,
u3C4z"[t A0  6   COUNT(DISTINCT PROD2) PROD2_CNT,ITPUB个人空间 D2gDy C3a ^?
  7   COUNT(DISTINCT PROD5) PROD3_CNTITPUB个人空间'QlbmMs
  8  FROMITPUB个人空间?wc.B&_V0Q
  9  (
0BI5U/P;_:Lc]%h0 10   SELECT PLAT_ID,
6yc$u4|0U BS-wwrSz0 11    PRODUCT_ID,
-xH)Q)@)V%[g x4P0 12    BUYER_ORGID,ITPUB个人空间8F Y.dn l/QhXU
 13    SENDER_ORGID,
-O Ln
T2lV$bJ9y&c0 14    DECODE(SOURCE_TYPE, '1', PRODUCT_ID) PROD1,ITPUB个人空间4u%O,l#?)zhV
 15    DECODE(SOURCE_TYPE, '2', PRODUCT_ID) PROD2,
w!dyq?U?Y%h0 16    DECODE(SOURCE_TYPE, '5', PRODUCT_ID) PROD5
8Z){"QX;?0 17   FROM ORD_HIT_COMMITPUB个人空间;^h O9kH^9d
 18   WHERE ENABLE_FLAG = '1'
f Cm;d6p[(n@x0 19  ) A,
)D;bem%LP0 20  (
tl#t.E6tZ @YY5Q0 21   SELECT CONNECT_BY_ROOT(ID) RID, ID FROM PLT_PLAT CONNECT BY PRIOR ID = PLAT_FATHERITPUB个人空间+}td(s8dh
 22  ) B
6J5N6z_}8s6_0 23  WHERE PLAT_ID = B.IDITPUB个人空间L2Bh$@3H])?)]
Om x{
 24  GROUP BY RID;

ID                         PROD_CNT  BUYER_CNT SENDER_CNT  PROD1_CNT  PROD2_CNT  PROD3_CNT
SZ$T
AbG:qd^B
Z0------------------------ ---------- ---------- ---------- ---------- ---------- ----------ITPUB个人空间"K:x;N&D!_1LHG]
30SH10000000000013180006      33189        439        953      24227          0       7884
;] e:l
c-z'[s;k0EMED10000000405828930000      32156        421        947      23596          0       7696ITPUB个人空间zB)i4n-y5N
EMED10000000405828940000      10374        173         77       8813          0        650
1]'uiXeq!L0EMED10000000405828960000       3199         11          1        836          0          0
|B's1X*F6p4f y/M0EMED10000000409959510000      11103         25         65       8848          0         55
'Fe
^$c3G1B)S _b0EMED10000000418536040000       2875          6          6       2240          0        187
8uS:S'M/E-O7K6u0EMED10000000419782960000       3199         11          1        836          0          0ITPUB个人空间f8G.~7Y5pQ2NUX
EMED10000000419784520000        177          2          2          0          0        177
`:nKUq;o'Wk g0EMED10000000419784550000        177          2          2          0          0        177
.Y1p @s@NP0EMED10000000420113440000        991         15          3          0          0        989
|q*}Ng5p0EMED10000000425870530000       2284          9          1       2284          0          0

h/VW k-a%p$aY"l6a0EMED10000000439749490000       7181         18         39       6807          0        282
W\+s5qotd@0EMED10000000439749500000       6103         10        115       4764          0          0ITPUB个人空间Ti$}@iYB
EMED10000000443853170000      10529         46        502          7          0       6259
\7Wx w3Z7[1vA0EMED10000000468245720000       5845         29         88       5299          0          0
5n(ov{ ^ D*Bw(w0EMED10000000479556110000       8430         24        104          0          0          0ITPUB个人空间\uF v!|
EMED10000000479556130000       7865         18        148       4392          0          0
s]3f2E/B6xD7B0EMED10000000479556140000       2007         19         64       2007          0          0ITPUB个人空间i&ux}X#`6_"X
EMED10000000479556170000       2777         14         74       2777          0          0ITPUB个人空间mswB
g~)^8Qt[
EMED10000000479556180000       5295         21        165       2705          0          0

已选择20行。

已用时间:  00: 01: 57.32

观察执行计划：

SQL> EXPLAIN PLAN FORITPUB个人空间3G9M.d%G*CPK`*@
  2  SELECT RID ID,
lL/g`d M
@l&|I0  3   COUNT(DISTINCT PRODUCT_ID) PROD_CNT,
yY+e Aa0  4   COUNT(DISTINCT BUYER_ORGID) BUYER_CNT,ITPUB个人空间]Q0o
q&T(j2~C
  5   COUNT(DISTINCT SENDER_ORGID) SENDER_CNT,ITPUB个人空间"MIQLW U
  6   COUNT(DISTINCT PROD1) PROD1_CNT,
:s
I(\#y3G K0  7   COUNT(DISTINCT PROD2) PROD2_CNT,
C \9NlAh-ZL0  8   COUNT(DISTINCT PROD5) PROD3_CNTITPUB个人空间o8_/P2vd8H
  9  FROMITPUB个人空间A5V3K/k&`$HK
 10  (ITPUB个人空间Pvat Au)^
 11   SELECT PLAT_ID,
XPc@4] U9X l9Q0 12    PRODUCT_ID,ITPUB个人空间'gp1KN6L{V8`
 13    BUYER_ORGID,
8@P1rg!fv0 14    SENDER_ORGID,
&V2V\)EC0 15    DECODE(SOURCE_TYPE, '1', PRODUCT_ID) PROD1,ITPUB个人空间Ot Qs-\9Wh(M4\V
 16    DECODE(SOURCE_TYPE, '2', PRODUCT_ID) PROD2,ITPUB个人空间a:o;ZL"JK0A;cz
 17    DECODE(SOURCE_TYPE, '5', PRODUCT_ID) PROD5ITPUB个人空间av$C5m"`&]o)Z
 18   FROM ORD_HIT_COMM
l
G~R7y4b0 19   WHERE ENABLE_FLAG = '1'ITPUB个人空间etg@UWB
 20  ) A,ITPUB个人空间y5I~j,]|
 21  (ITPUB个人空间G4b/Io/tiaO
 22   SELECT CONNECT_BY_ROOT(ID) RID, ID FROM PLT_PLAT CONNECT BY PRIOR ID = PLAT_FATHER
iF+Y(xjUu%{0 23  ) BITPUB个人空间re
k Y9Z\
 24  WHERE PLAT_ID = B.IDITPUB个人空间 Ia [[$C2_
 25  GROUP BY RID;

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
W's&EfJ"kJ0-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
U+Wy0?%B7`6R?0| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)|
"y'r:^K7MuLG \4m0-------------------------------------------------------------------------------------
e4[ |p lA#M&a%Z0|   0 | SELECT STATEMENT                |              |  1622K|  241M| 30026   (2)|ITPUB个人空间
hQ~6GaXg/t2yg
|   1 |  SORT GROUP BY                  |              |  1622K|  241M| 30026   (2)|ITPUB个人空间_&`9V$\ W
|   2 |   HASH JOIN                     |              |  1622K|  241M| 29886   (1)|ITPUB个人空间&| ~}m\Q
|   3 |    VIEW                         |              |    33 |  1716 |     2   (0)|
;i dkc8W Y0|   4 |     CONNECT BY WITHOUT FILTERING|              |       |       |            |ITPUB个人空间XDv H6O$It)O
|   5 |      TABLE ACCESS FULL          | PLT_PLAT     |    33 |  1650 |     2   (0)|ITPUB个人空间kL:Qz G6X
|   6 |    TABLE ACCESS FULL            | ORD_HIT_COMM |  1622K|  160M| 29868   (1)|ITPUB个人空间h6L}+ko3\6o&f;U
-------------------------------------------------------------------------------------

已选择16行。

PLT_PLAT不需要多次关联，而且笛卡儿积已经去掉，连接方式也变成了HASH JOIN，通过使用Oracle提供的新特性，将SQL的执行时间从1天，提高到了2分钟。熟悉Oracle提供的特性是多么的重要啊。