[转] 一次sql优化的小总结 ( nested loop to hash join )

以前公司的一位同事让优化个sql语句，说跑的很慢，而且很奇怪的是，不加过滤条件，sql不慢，加了一个过滤条件，sql就变慢，sql是这样的：

ITPUB个人空间/Tp9C}yWZ

没加过滤条件之前的sql及其执行计划，这个跑的快:ITPUB个人空间_l*@F2uK
j
SQL> explain plan for SELECT AL3.IE_NAME, AL4.COUNTRY_NAME, AL6.ORG_LEV2_NAME, AL1.GOODS_NUM
3zP]m0dJ)Y0FROM CIQDSS.FACT_IE_GOODS_CIQ_SUM AL1,ITPUB个人空间 qe:NL8je2h
CIQDSS.DIM_DATE AL2,ITPUB个人空间']]J;KK7u|o@
CIQDSS.DIM_IE AL3,ITPUB个人空间$Hf%Rk3a|p;j3enW
CIQDSS.DIM_COUNTRY AL4,ITPUB个人空间q`\7C.z L8W8mB:R
CIQDSS.DIM_CIQ_CODE AL5,ITPUB个人空间EIOJ(ZXr
CIQDSS.DIM_INSP_ORG AL6
(\3@AC5EY7o*E0WHERE (AL2.DATE_ID=AL1.DATE_ID AND
r/M#D2E'H+^"d~X
q0AL3.IE_KEY=AL1.IE_KEY AND
8zbleei c0AL4.COUNTRY_KEY=AL1.COUNTRY_KEY AND
UA v,YP^0V5OV0AL5.CIQ_KEY=AL1.CIQ_KEY AND
| y-rd t]V0AL6.ORG_KEY=AL1.ORG_KEY) AND
!Y9GU [/Y ?^0(AL6.END_DATE=to_date('99990101','yyyymmdd') ANDITPUB个人空间@l1T1w5Ebg
AL5.END_DATE=to_date('99990101','yyyymmdd') ANDITPUB个人空间_{1K/c'D,DF,M
AL3.END_DATE=to_date('99990101','yyyymmdd') AND
N4[B5M#AmS5J/v @0AL4.END_DATE=to_date('99990101','yyyymmdd'));

已解释。

已用时间: 00: 00: 00.01
"L:[+k(A:j/xd017:52:39 SQL>
DfpQL,x6v*C017:53:06 SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUTITPUB个人空间IU3R6A2t,Mq*n"d
----------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------ITPUB个人空间"l;O s I9q
K#@ A(`'s
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |ITPUB个人空间qPu!k%V XK
---------------------------------------------------------------------------------------------------
0x`g1U*@FX7h JP;]0| 0 | SELECT STATEMENT | | 24417 | 5508K| 3129 | | |ITPUB个人空间U&V:fU/s6@
| 1 | NESTED LOOPS | | 24417 | 5508K| 3129 | | |ITPUB个人空间8J7q3E_1WEp
| 2 | HASH JOIN | | 41 | 9225 | 3129 | | |ITPUB个人空间k3o8OH(D E{_
| 3 | TABLE ACCESS FULL | DIM_CIQ_CODE | 78 | 1716 | 7 | | |
*Na EqX'[ i5\I \.|i0| 4 | HASH JOIN | | 1244 | 246K| 3121 | | |
fM ?8N[1MN,_0| 5 | TABLE ACCESS FULL | DIM_IE | 1 | 34 | 2 | | |
}0Y(z
p#OG i/L [0| 6 | HASH JOIN | | 1517 | 250K| 3118 | | |
kFwE$}9_Q,c0| 7 | TABLE ACCESS FULL | DIM_INSP_ORG | 11 | 814 | 2 | | |
[ vs?5^ |`0| 8 | HASH JOIN | | 75585 | 7012K| 3114 | | |
\(D'{;Q+zf9pztb0| 9 | TABLE ACCESS FULL | DIM_COUNTRY | 4 | 296 | 2 | | |
JS,u+lxqf8N0| 10 | PARTITION RANGE ALL| | | | | 1 | 84 |ITPUB个人空间U
^}~CoS|
| 11 | TABLE ACCESS FULL | FACT_IE_GOODS_CIQ_SUM | 4250K| 85M| 3094 | 1 | 84 |ITPUB个人空间-BHw s]MB^
| 12 | INDEX UNIQUE SCAN | PK_DIM_DATE | 7107 | 42642 | | | |ITPUB个人空间#imG!ZMC/rh
u
---------------------------------------------------------------------------------------------------

Note: cpu costing is off, 'PLAN_TABLE' is old version

已选择20行。

r tx.}$~6]0加过滤条件之后的sql及其执行计划，这个跑的慢:
Y6p.x$A'B1z0SQL> explain plan for SELECT AL3.IE_NAME, AL4.COUNTRY_NAME, AL6.ORG_LEV2_NAME, AL1.GOODS_NUMITPUB个人空间,^._0{
Vx8_;@
FROM CIQDSS.FACT_IE_GOODS_CIQ_SUM AL1,
+Z+]
_"F+\j
en/a0CIQDSS.DIM_DATE AL2,ITPUB个人空间-U0y+uUNL
CIQDSS.DIM_IE AL3,ITPUB个人空间e$^l(}.J f,?
CIQDSS.DIM_COUNTRY AL4,ITPUB个人空间6G6z}8Vh,@
CIQDSS.DIM_CIQ_CODE AL5,
;wu"O;F|0CIQDSS.DIM_INSP_ORG AL6
c.q-?AZAv0WHERE (AL2.DATE_ID=AL1.DATE_ID ANDITPUB个人空间,\(g8`q9D B7{v
AL3.IE_KEY=AL1.IE_KEY ANDITPUB个人空间4c"m
m?hLi1p9o
AL4.COUNTRY_KEY=AL1.COUNTRY_KEY AND
j%k_L!s8`0AL5.CIQ_KEY=AL1.CIQ_KEY AND
;H4W{*m/e0AL6.ORG_KEY=AL1.ORG_KEY) ANDITPUB个人空间dl@{1w}/s
(AL6.END_DATE=to_date('99990101','yyyymmdd') AND
ok V\+l4_8lg0AL5.END_DATE=to_date('99990101','yyyymmdd') AND
v
C|:e7RqrX9L0AL3.END_DATE=to_date('99990101','yyyymmdd') ANDITPUB个人空间u@ I*?h?b$R
AL4.END_DATE=to_date('99990101','yyyymmdd')ITPUB个人空间'Ta v1Y TGZ!|8F{
AL2.DATE_ID BETWEEN '20000101' AND '20400520') ; --这个就是那个过滤条件

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
pBp.|,mU0----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
p,V"e9c6t JC1du!c0| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |

f3H(g(qX3x2u0----------------------------------------------------------------------------------------------------
-j/e+^~tER0| 0 | SELECT STATEMENT | | 61 | 14091 | 133 | | |ITPUB个人空间De+w~||8h"Y(`)a9_n
| 1 | HASH JOIN | | 61 | 14091 | 133 | | |ITPUB个人空间*SrTY$}W0r
| 2 | TABLE ACCESS FULL | DIM_CIQ_CODE | 78 | 1716 | 7 | | |ITPUB个人空间N&s-AVR V,VC
| 3 | NESTED LOOPS | | 1842 | 375K| 125 | | |ITPUB个人空间'UH ]/{M
| 4 | HASH JOIN | | 1244 | 246K| 125 | | |ITPUB个人空间xM/I fg
| 5 | TABLE ACCESS FULL | DIM_IE | 1 | 34 | 2 | | |ITPUB个人空间*J+{!u;{1C;Q U/v
| 6 | HASH JOIN | | 1517 | 250K| 122 | | |
X2Y7s"lS;[0| 7 | TABLE ACCESS FULL | DIM_INSP_ORG | 11 | 814 | 2 | |ITPUB个人空间v,Au`o(l:j"u-DR
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_IE_GOODS_CIQ_SUM | 18480 | 378K| 29 |ITPUB个人空间
s9?#d/MM)pD)~2r-W]
| 9 | NESTED LOOPS | | 75585 | 7012K| 118 | | |ITPUB个人空间~"DK moUd#eP8J;A
| 10 | TABLE ACCESS FULL | DIM_COUNTRY | 4 | 296 | 2 | |ITPUB个人空间0r I"G/}"[L~&QR:Zw
| 11 | PARTITION RANGE ALL | | | | | 1 | 84 |ITPUB个人空间;ex6^)Td)@
| 12 | INDEX RANGE SCAN | FACT_IE_GOODS_CIQ_SUM | 18480 | | 25 | 1 | 84 |
Qu9v6V+rmb!m$r0| 13 | INDEX UNIQUE SCAN | PK_DIM_DATE | 18 | 108 | | | |ITPUB个人空间!KHR4[6I3`
----------------------------------------------------------------------------------------------------

Note: cpu costing is off, 'PLAN_TABLE' is old version

已选择21行。

?+Z,X9Yk6aM@!{0了解到FACT_IE_GOODS_CIQ_SUM表是大表，几百万行记录，其它的表都是小表，不超过几十万行。
t(],v~NI0开始研究这2个执行计划：ITPUB个人空间4AKG5m!b6wn-MC
第一个快的sql，FACT_IE_GOODS_CIQ_SUM和DIM_COUNTRY作连接的时候使用了hash join；ITPUB个人空间A6V_~MD_/y
第二个慢的sql，FACT_IE_GOODS_CIQ_SUM和DIM_COUNTRY作连接的时候使用了nested loop；

凭经验，第一感觉判断是nested loop是sql变慢的主要原因。ITPUB个人空间3Yfr ty"i;e0_7q
cbo 采用nested loop是由于，cbo认为dim_country过滤出来只有4条记录，作为nested loop的驱动表很合适，另外，sql语句加上过滤条件以后，FACT_IE_GOODS_CIQ_SUM表可以根据条件进行分区过滤，这样就降低了 FACT_IE_GOODS_CIQ_SUM表的访问成本，这可以使nested loop操作变快。
9Eo-NA!\+l%u0i})Y0可实际上这些表的统计信息和实际情况相差很多，dim_country表有几十万记录，通过条件过滤出来的也不止4条，所以nested loop的循环次数也不止4次，而过多的循环往往是nested loop慢的原因。ITPUB个人空间.V%j2{$BL&F3j_
所 以，sql语句加上条件以后cbo通过不准确的信息估算出nested loop是最好的连接方式。但实际并非如此，由于2个表过滤以后的结果集都很大，采用hash join会快，nested loop会慢，而cbo错误的选择了nested loop，所以sql变慢了。

调整措施：

告诉同事让其修改sql，加上use_hash的提示，强制走hash join。ITPUB个人空间B/D}9G{0Zp4w
修改之后的结果：

SQL> explain plan for SELECT/*+ use_hash(al1 al4) */ITPUB个人空间(\l7c'B ^(RS
AL3.IE_NAME, AL4.COUNTRY_NAME, AL6.ORG_LEV2_NAME, AL1.GOODS_NUMITPUB个人空间6|jVh$J9m!u
FROM CIQDSS.FACT_IE_GOODS_CIQ_SUM AL1,
0D's"N"sy)opJ0CIQDSS.DIM_DATE AL2,ITPUB个人空间*pY#fXm3EWy
CIQDSS.DIM_IE AL3,ITPUB个人空间K%m;J r#a&fNi
Hm
CIQDSS.DIM_COUNTRY AL4,ITPUB个人空间2r8Fx ~1V'~6p`-vU/m
CIQDSS.DIM_CIQ_CODE AL5,
kw {F.Q"?&T0CIQDSS.DIM_INSP_ORG AL6ITPUB个人空间 X'odOt \
WHERE (AL2.DATE_ID=AL1.DATE_ID ANDITPUB个人空间H3v4FZ7aX5W
AL3.IE_KEY=AL1.IE_KEY AND
.Xca,R8Eb}vT!R0AL4.COUNTRY_KEY=AL1.COUNTRY_KEY ANDITPUB个人空间:ii@;C#G\9Hw$u
AL5.CIQ_KEY=AL1.CIQ_KEY AND
7xEGAEw;H0AL6.ORG_KEY=AL1.ORG_KEY) ANDITPUB个人空间mm-Aub|
(AL6.END_DATE=to_date('99990101','yyyymmdd') ANDITPUB个人空间*TI,jG4{4Wq/i
AL5.END_DATE=to_date('99990101','yyyymmdd') ANDITPUB个人空间Y3c(tf$u+K4^)p
AL3.END_DATE=to_date('99990101','yyyymmdd') AND
w&}T#|,Tx0AL4.END_DATE=to_date('99990101','yyyymmdd') AND
$JaAo8x j2M0AL2.DATE_ID BETWEEN '20000101' AND '20400520') ;

已解释。

已用时间: 00: 00: 00.01ITPUB个人空间6_-s+?,P{8f2|:j
18:09:08 SQL>ITPUB个人空间H0N*^?8eT
18:09:32 SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUTITPUB个人空间!DP[:^2jER-k6{(y
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------ITPUB个人空间V%~FN*P.p*k
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
hC-`.A9h-}~0----------------------------------------------------------------------------------------------------ITPUB个人空间J*Ed)MQM4HE$Ux
| 0 | SELECT STATEMENT | | 61 | 14091 | 861 | | |
@:iMM TU@)_*F E8{0| 1 | HASH JOIN | | 61 | 14091 | 861 | | |
T*YUFj'\&{0| 2 | TABLE ACCESS FULL | DIM_CIQ_CODE | 78 | 1716 | 7 | | |ITPUB个人空间
We l!Ile+s9U
| 3 | NESTED LOOPS | | 1842 | 375K| 853 | | |
[9bcFru0| 4 | HASH JOIN | | 1244 | 246K| 853 | | |
F&x:Q
H%h~0| 5 | TABLE ACCESS FULL | DIM_IE | 1 | 34 | 2 | | |
oRp'YB6s1SA0| 6 | HASH JOIN | | 1517 | 250K| 850 | | |
/T N4zmw0| 7 | TABLE ACCESS FULL | DIM_INSP_ORG | 11 | 814 | 2 | | |
$f:t|6@iV0U8w|0| 8 | HASH JOIN | | 75585 | 7012K| 846 | | |ITPUB个人空间Iz6B)T'Rd
| 9 | TABLE ACCESS FULL | DIM_COUNTRY | 4 | 296 | 2 | | |
dw;u
w _z%m V0| 10 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
\*\K} U6|H,p0| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_IE_GOODS_CIQ_SUM | 4250K| 85M| 826 |
;T1fH(rv0| 12 | INDEX RANGE SCAN | FACT_IE_GOODS_CIQ_SUM | 18480 | | 26 | KEY | KEY |
}NsFl&Jhgj0| 13 | INDEX UNIQUE SCAN | PK_DIM_DATE | 2 | 12 | | | |
_/@)B$Fl},H$x3}0----------------------------------------------------------------------------------------------------

Note: cpu costing is off, 'PLAN_TABLE' is old version

已选择21行。

;tY0?U1|)Z0]n0同事说，sql变快了，而且比不加条件的那个还要快(其实这也是应该的)。

z5c'p1K9uj |0

ITPUB个人空间4M;LV-_dj

ITPUB个人空间7N,CO tPo,o2QP