学习oracle技术,每天学一点,每天进步一点
oracle in与exists 的区别
上一篇 / 下一篇 2008-04-14 14:52:28 / 个人分类：pl/sql
表的分布情况 采用cbo 数据和索引都已分析
tb_role 为8960行 建有parent_id的索引ITPUB个人空间3]4ws `X x.p
tb_user 为8541行 建有role_id的索引ITPUB个人空间;U/i7|.pjdzO
SQL> select id, role_id, login
3L;]%p[w4B0 2 from tb_user eITPUB个人空间^7n*ei V `
3 where Exists (Select ID
1`-L^@\#Y2m0 4 From tb_role fITPUB个人空间g_ L0A(v f S;v
5 where e.role_id = f.idITPUB个人空间 w zc ?Z#@ P
6 Connect By Prior Id = parent_idITPUB个人空间?%[(? xzd
7 Start With Id = 1);
;W hMu ^%[Hl y0
k%L a*C1or08369 rows selected.
c}\Ew^ `.yepPjU0
L'?G k8u$wQ.JI!D0ITPUB个人空间4{Y0S3u ov
Execution PlanITPUB个人空间6T'?2D3?!I"|%A
----------------------------------------------------------
x v r jc `Z^0 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1292 Card=428 Bytes=ITPUB个人空间T$o,[H,D{/?M j
9416)
/r'fe F x w0
hpAUZL SK0 1 0 FILTERITPUB个人空间o*vla*| aqh
2 1 TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=428 Byte
0_ x-Ih/PL V.K0 s=9416)
0D9dG Ap0ITPUB个人空间 w1i G*X:j uU w5^%v0{7UI
3 1 FILTER
,~#d0_Y/`:t [0 4 3 CONNECT BY (WITH FILTERING)
|!c b9HW)x"P9`0 5 4 NESTED LOOPSITPUB个人空间 v5\'I]fm j5| }
6 5 INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)ITPUB个人空间%Q;MV&k r)G3xl&q
(Cost=1 Card=1 Bytes=5)ITPUB个人空间W)I9|d r(W J'@
`W s I(X%n0 7 5 TABLE ACCESS (BY USER ROWID) OF 'tb_role'ITPUB个人空间d5m%@*M,\0R h d
8 4 NESTED LOOPS
Yw [*G F,O5_4x0 9 8 BUFFER (SORT)ITPUB个人空间 k&Y2[ ~Y9e P Z2hg
10 9 CONNECT BY PUMPITPUB个人空间1pI{.q$`+~
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'tb_role' (ITPUB个人空间7q`-h8ck
Cost=3 Card=18 Bytes=162)
}({H [9`tD0
kX?3g d3\&[ vO9v0 12 11 INDEX (RANGE SCAN) OF 'INX_tb_role_PARENT_ITPUB个人空间4D? Q@s%j
ID' (NON-UNIQUE) (Cost=1 Card=18)ITPUB个人空间#S%~7?s*Tr0C
S2u q8i{0ITPUB个人空间#{,Sd'W#x)[0iL
HEr d](dr x V0ITPUB个人空间 n M:D a?:DK5w)K:aB.v
/]#pu f*fq0StatisticsITPUB个人空间Z)v&vXIm/`~{[
----------------------------------------------------------
] J*q#L!fi'n(]N2|C0 0 recursive calls
Bj O V/?-_0 0 db block getsITPUB个人空间+KcJCc-JY&C
84313553 consistent gets
9H o&xwjY0 0 physical reads
_g \[x*F0 0 redo sizeITPUB个人空间H ?0^"F-H)U
224163 bytes sent via SQL*Net to clientITPUB个人空间ZR!n ^s!O|B
6553 bytes received via SQL*Net from client
JW ]}F`0 559 SQL*Net roundtrips to/from clientITPUB个人空间!rSz,l&T,r$\:z
142392 sorts (memory)
oF1K%D2U ] ?0 0 sorts (disk)ITPUB个人空间r~;mj A \4zE
8369 rows processedITPUB个人空间0z)W(IyM8V D
ITPUB个人空间.O?;Ci#w1{{;v
SQL>ITPUB个人空间l#F$kVv G(w ~g
一致性读达到 84313553 consistent gets ,而造成数据很慢查出来
5`+Qrr(QU0
S0R!R&]-h J0改用in的方式 一致性读马上降低到10250 ,马上查出数据ITPUB个人空间[$c,b:i$C
SQL> select id, role_id, login
1F6q V#AI Ll!S#vB'q0 2 from tb_user eITPUB个人空间g;[ ]\$Q:y
3 where role_id in (Select ID
#X f y D.y;Tp)~| KR0 4 From tb_role f
5l;swJ fB0D0 5 Connect By Prior Id = parent_idITPUB个人空间!j%A fP6^1W
6 Start With Id = 1);ITPUB个人空间F3| W _&Y8j8}
J+aoz6oo'c(p^:`08369 rows selected.
})j8ri0J d0
1[-t U.w2G-T7}x0ITPUB个人空间bE3_r(PNuC7t8i l
Execution Plan
(P9MuFfv.p0----------------------------------------------------------
Nj??"cGt+xF0 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=14 Card=18 Bytes=630
:b ]LpO \3Kh0 )ITPUB个人空间}O^"t5D,I i } jO nV
ITPUB个人空间sJGyb \
1 0 HASH JOIN (SEMI) (Cost=14 Card=18 Bytes=630)
z Z$l8Aj G Z0 2 1 TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=8556 BytITPUB个人空间+@y] n ev(re&w1^
es=188232)
-j%~ K5`#w `~q(w!o;W0ITPUB个人空间c d#E4o [p-N
3 1 VIEW OF 'VW_NSO_1' (Cost=3 Card=18 Bytes=234)
,^H8_kv-cGC8d"h2g0 4 3 CONNECT BY (WITH FILTERING)ITPUB个人空间t%yvL9p)s e-P
5 4 NESTED LOOPS
:|P Q$\5h2{)c0 6 5 INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)
Q'Fa ?8y8j0 (Cost=1 Card=1 Bytes=5)
$? R iV3s(nK0ITPUB个人空间 Kg*]p6{ m$c/_
7 5 TABLE ACCESS (BY USER ROWID) OF 'tb_role'
G%VY B _0 8 4 NESTED LOOPSITPUB个人空间 JN k"Q1N8oA
9 8 BUFFER (SORT)ITPUB个人空间-q\d _,d Kj U:q
10 9 CONNECT BY PUMPITPUB个人空间%I(Dp5~D)GPw
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'tb_role' (ITPUB个人空间:p*tI#Sl O
Cost=3 Card=18 Bytes=162)
bt r&zC9y%]0
;R E x+tk$e Q0 12 11 INDEX (RANGE SCAN) OF 'INX_tb_role_PARENT_ITPUB个人空间0i1PF ^7g a
ID' (NON-UNIQUE) (Cost=1 Card=18)ITPUB个人空间|yI*oW
ITPUB个人空间X Q1C%N5I&|_
'R B@ d4H h k0ITPUB个人空间6J Ap8N$b
ITPUB个人空间*q E-l~~3k
6t H ss fw9c4t#A-v8S5W0StatisticsITPUB个人空间:B'P{,{ Yxm
----------------------------------------------------------
VunR;s*? S0 0 recursive calls
/Yk.frV W1C0 0 db block gets
e8Y0NK _6G D0 10250 consistent gets ITPUB个人空间q)Nn"o Z
0 physical reads
]wF/qT3^Z{0 0 redo sizeITPUB个人空间4P H.P{2_ Nj
224163 bytes sent via SQL*Net to clientITPUB个人空间*Yh7u-tA {*Df
6553 bytes received via SQL*Net from client
)v;n @NV u-j sl0 559 SQL*Net roundtrips to/from client
(Q0N9p j6a ^0 17 sorts (memory)
g!Lde9k1Ba0 0 sorts (disk)
X-~ ??)@-^1]0 8369 rows processed
w!?Bf8k0
tb_user 为8541行 建有role_id的索引ITPUB个人空间;U/i7|.pjdzO
SQL> select id, role_id, login
3L;]%p[w4B0 2 from tb_user eITPUB个人空间^7n*ei V `
3 where Exists (Select ID
1`-L^@\#Y2m0 4 From tb_role fITPUB个人空间g_ L0A(v f S;v
5 where e.role_id = f.idITPUB个人空间 w zc ?Z#@ P
6 Connect By Prior Id = parent_idITPUB个人空间?%[(? xzd
7 Start With Id = 1);
;W hMu ^%[Hl y0
k%L a*C1or08369 rows selected.
c}\Ew^ `.yepPjU0
L'?G k8u$wQ.JI!D0ITPUB个人空间4{Y0S3u ov
Execution PlanITPUB个人空间6T'?2D3?!I"|%A
----------------------------------------------------------
x v r jc `Z^0 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1292 Card=428 Bytes=ITPUB个人空间T$o,[H,D{/?M j
9416)
/r'fe F x w0
hpAUZL SK0 1 0 FILTERITPUB个人空间o*vla*| aqh
2 1 TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=428 Byte
0_ x-Ih/PL V.K0 s=9416)
0D9dG Ap0ITPUB个人空间 w1i G*X:j uU w5^%v0{7UI
3 1 FILTER
,~#d0_Y/`:t [0 4 3 CONNECT BY (WITH FILTERING)
|!c b9HW)x"P9`0 5 4 NESTED LOOPSITPUB个人空间 v5\'I]fm j5| }
6 5 INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)ITPUB个人空间%Q;MV&k r)G3xl&q
(Cost=1 Card=1 Bytes=5)ITPUB个人空间W)I9|d r(W J'@
`W s I(X%n0 7 5 TABLE ACCESS (BY USER ROWID) OF 'tb_role'ITPUB个人空间d5m%@*M,\0R h d
8 4 NESTED LOOPS
Yw [*G F,O5_4x0 9 8 BUFFER (SORT)ITPUB个人空间 k&Y2[ ~Y9e P Z2hg
10 9 CONNECT BY PUMPITPUB个人空间1pI{.q$`+~
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'tb_role' (ITPUB个人空间7q`-h8ck
Cost=3 Card=18 Bytes=162)
}({H [9`tD0
kX?3g d3\&[ vO9v0 12 11 INDEX (RANGE SCAN) OF 'INX_tb_role_PARENT_ITPUB个人空间4D? Q@s%j
ID' (NON-UNIQUE) (Cost=1 Card=18)ITPUB个人空间#S%~7?s*Tr0C
S2u q8i{0ITPUB个人空间#{,Sd'W#x)[0iL
HEr d](dr x V0ITPUB个人空间 n M:D a?:DK5w)K:aB.v
/]#pu f*fq0StatisticsITPUB个人空间Z)v&vXIm/`~{[
----------------------------------------------------------
] J*q#L!fi'n(]N2|C0 0 recursive calls
Bj O V/?-_0 0 db block getsITPUB个人空间+KcJCc-JY&C
84313553 consistent gets
9H o&xwjY0 0 physical reads
_g \[x*F0 0 redo sizeITPUB个人空间H ?0^"F-H)U
224163 bytes sent via SQL*Net to clientITPUB个人空间ZR!n ^s!O|B
6553 bytes received via SQL*Net from client
JW ]}F`0 559 SQL*Net roundtrips to/from clientITPUB个人空间!rSz,l&T,r$\:z
142392 sorts (memory)
oF1K%D2U ] ?0 0 sorts (disk)ITPUB个人空间r~;mj A \4zE
8369 rows processedITPUB个人空间0z)W(IyM8V D
ITPUB个人空间.O?;Ci#w1{{;v
SQL>ITPUB个人空间l#F$kVv G(w ~g
一致性读达到 84313553 consistent gets ,而造成数据很慢查出来
5`+Qrr(QU0
S0R!R&]-h J0改用in的方式 一致性读马上降低到10250 ,马上查出数据ITPUB个人空间[$c,b:i$C
SQL> select id, role_id, login
1F6q V#AI Ll!S#vB'q0 2 from tb_user eITPUB个人空间g;[ ]\$Q:y
3 where role_id in (Select ID
#X f y D.y;Tp)~| KR0 4 From tb_role f
5l;swJ fB0D0 5 Connect By Prior Id = parent_idITPUB个人空间!j%A fP6^1W
6 Start With Id = 1);ITPUB个人空间F3| W _&Y8j8}
J+aoz6oo'c(p^:`08369 rows selected.
})j8ri0J d0
1[-t U.w2G-T7}x0ITPUB个人空间bE3_r(PNuC7t8i l
Execution Plan
(P9MuFfv.p0----------------------------------------------------------
Nj??"cGt+xF0 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=14 Card=18 Bytes=630
:b ]LpO \3Kh0 )ITPUB个人空间}O^"t5D,I i } jO nV
ITPUB个人空间sJGyb \
1 0 HASH JOIN (SEMI) (Cost=14 Card=18 Bytes=630)
z Z$l8Aj G Z0 2 1 TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=8556 BytITPUB个人空间+@y] n ev(re&w1^
es=188232)
-j%~ K5`#w `~q(w!o;W0ITPUB个人空间c d#E4o [p-N
3 1 VIEW OF 'VW_NSO_1' (Cost=3 Card=18 Bytes=234)
,^H8_kv-cGC8d"h2g0 4 3 CONNECT BY (WITH FILTERING)ITPUB个人空间t%yvL9p)s e-P
5 4 NESTED LOOPS
:|P Q$\5h2{)c0 6 5 INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)
Q'Fa ?8y8j0 (Cost=1 Card=1 Bytes=5)
$? R iV3s(nK0ITPUB个人空间 Kg*]p6{ m$c/_
7 5 TABLE ACCESS (BY USER ROWID) OF 'tb_role'
G%VY B _0 8 4 NESTED LOOPSITPUB个人空间 JN k"Q1N8oA
9 8 BUFFER (SORT)ITPUB个人空间-q\d _,d Kj U:q
10 9 CONNECT BY PUMPITPUB个人空间%I(Dp5~D)GPw
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'tb_role' (ITPUB个人空间:p*tI#Sl O
Cost=3 Card=18 Bytes=162)
bt r&zC9y%]0
;R E x+tk$e Q0 12 11 INDEX (RANGE SCAN) OF 'INX_tb_role_PARENT_ITPUB个人空间0i1PF ^7g a
ID' (NON-UNIQUE) (Cost=1 Card=18)ITPUB个人空间|yI*oW
ITPUB个人空间X Q1C%N5I&|_
'R B@ d4H h k0ITPUB个人空间6J Ap8N$b
ITPUB个人空间*q E-l~~3k
6t H ss fw9c4t#A-v8S5W0StatisticsITPUB个人空间:B'P{,{ Yxm
----------------------------------------------------------
VunR;s*? S0 0 recursive calls
/Yk.frV W1C0 0 db block gets
e8Y0NK _6G D0 10250 consistent gets ITPUB个人空间q)Nn"o Z
0 physical reads
]wF/qT3^Z{0 0 redo sizeITPUB个人空间4P H.P{2_ Nj
224163 bytes sent via SQL*Net to clientITPUB个人空间*Yh7u-tA {*Df
6553 bytes received via SQL*Net from client
)v;n @NV u-j sl0 559 SQL*Net roundtrips to/from client
(Q0N9p j6a ^0 17 sorts (memory)
g!Lde9k1Ba0 0 sorts (disk)
X-~ ??)@-^1]0 8369 rows processed
w!?Bf8k0
目前修改sql有这几种方式:
1.改成表的连接写法
select id, role_id, loginITPUB个人空间 O {C8x Sf L
from tb_user e,(Select IDITPUB个人空间+e c"]V[wjZg
From tb_role f
Xo.O4Hn _0 Connect By Prior Id = parent_idITPUB个人空间]3E~9C+R#x{
Start With Id = 1)d
:z6F,{0}1v;p#L"U0 where d.id = e.role_id;
from tb_user e,(Select IDITPUB个人空间+e c"]V[wjZg
From tb_role f
Xo.O4Hn _0 Connect By Prior Id = parent_idITPUB个人空间]3E~9C+R#x{
Start With Id = 1)d
:z6F,{0}1v;p#L"U0 where d.id = e.role_id;
2.改成用in的方式 如上所示
3.改成exists 的写法
SQL> select id, role_id, login
M+^ o4K_0 2 from tb_user e
P*Zw#i8y j2A n0 3 where Exists (select idITPUB个人空间/F,aS S*S6^
4 from (Select IDITPUB个人空间H2s4js^ n V&Y&p-W
5 From tb_role f
m.M_ j\`)h/`0 6 Connect By Prior Id = parent_idITPUB个人空间,] c [9x`([ H!GZg
7 Start With Id = 1)
BS {yJM)H9F p:` DJ5\0 8 where id = e.role_id);ITPUB个人空间eN&rh-_ `'?B
M+^ o4K_0 2 from tb_user e
P*Zw#i8y j2A n0 3 where Exists (select idITPUB个人空间/F,aS S*S6^
4 from (Select IDITPUB个人空间H2s4js^ n V&Y&p-W
5 From tb_role f
m.M_ j\`)h/`0 6 Connect By Prior Id = parent_idITPUB个人空间,] c [9x`([ H!GZg
7 Start With Id = 1)
BS {yJM)H9F p:` DJ5\0 8 where id = e.role_id);ITPUB个人空间eN&rh-_ `'?B
具体讨论,可以参考以下链接:
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
标题搜索
数据统计
- 访问量: 15402
- 日志数: 276
- 图片数: 2
- 建立时间: 2007-12-11
- 更新时间: 2008-08-29
