nestloop

嵌套循环的程序逻辑相当于

for i in .. loop
for j in .. loop
if (matched)
output
end if;
end loop;
end loop;

所以它相对于merge join和hash join来说比较快的返回1行

因为merge join要先sort两个表之后,才开始做join
hash join要先构造驱动表的hash table

alter session set events '10046 trace name context forever, level 12';
用10046跟踪了一下：怎么看“第一次fetch的结果都是1行”呢，还有每次fetch返回多少行呢？
以下是部分dump内容：
PARSE #3:c=10000,e=4853,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=36791549501158
BINDS #3:
EXEC #3:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=36791549501537
WAIT #3: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=36791549501601
WAIT #3: nam='db file sequential read' ela= 16768 file#=21 block#=342321 blocks=1 obj#=101912 tim=36791549518892
WAIT #3: nam='db file sequential read' ela= 5761 file#=5 block#=2104784 blocks=1 obj#=101364 tim=36791549524776
FETCH #3:c=0,e=23147,p=2,cr=8,cu=0,mis=0,r=1,dep=0,og=1,tim=36791549524813
WAIT #3: nam='SQL*Net message from client' ela= 1000 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549525871
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549525936
WAIT #3: nam='db file sequential read' ela= 4547 file#=5 block#=2152085 blocks=1 obj#=101364 tim=36791549530544
WAIT #3: nam='db file sequential read' ela= 4924 file#=6 block#=551134 blocks=1 obj#=101364 tim=36791549535672
WAIT #3: nam='db file sequential read' ela= 2882 file#=6 block#=555031 blocks=1 obj#=101364 tim=36791549538623
FETCH #3:c=0,e=12833,p=3,cr=6,cu=0,mis=0,r=15,dep=0,og=1,tim=36791549538736
WAIT #3: nam='SQL*Net message from client' ela= 5247 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549544014
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549544064
WAIT #3: nam='db file sequential read' ela= 2388 file#=6 block#=575242 blocks=1 obj#=101364 tim=36791549546492
WAIT #3: nam='db file sequential read' ela= 7772 file#=7 block#=773258 blocks=1 obj#=101364 tim=36791549554371
WAIT #3: nam='db file sequential read' ela= 1887 file#=5 block#=2104657 blocks=1 obj#=101364 tim=36791549556341
WAIT #3: nam='db file sequential read' ela= 6154 file#=5 block#=2152238 blocks=1 obj#=101364 tim=36791549562559
WAIT #3: nam='db file sequential read' ela= 5853 file#=6 block#=556858 blocks=1 obj#=101364 tim=36791549568455
FETCH #3:c=10000,e=24435,p=5,cr=13,cu=0,mis=0,r=15,dep=0,og=1,tim=36791549568477
WAIT #3: nam='SQL*Net message from client' ela= 5006 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549573509
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549573550
WAIT #3: nam='db file sequential read' ela= 11600 file#=5 block#=2104670 blocks=1 obj#=101364 tim=36791549585223
WAIT #3: nam='db file sequential read' ela= 4392 file#=5 block#=2152239 blocks=1 obj#=101364 tim=36791549589652
WAIT #3: nam='db file sequential read' ela= 7358 file#=6 block#=551246 blocks=1 obj#=101364 tim=36791549597050
WAIT #3: nam='db file sequential read' ela= 3855 file#=6 block#=575243 blocks=1 obj#=101364 tim=36791549600982
WAIT #3: nam='db file sequential read' ela= 938 file#=21 block#=342323 blocks=1 obj#=101912 tim=36791549601973
WAIT #3: nam='db file sequential read' ela= 3873 file#=5 block#=2235392 blocks=1 obj#=101364 tim=36791549605900
FETCH #3:c=0,e=32438,p=6,cr=17,cu=0,mis=0,r=15,dep=0,og=1,tim=36791549605973
WAIT #3: nam='SQL*Net message from client' ela= 4789 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549610791
WAIT #3: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=101364 tim=36791549610836
WAIT #3: nam='db file sequential read' ela= 4063 file#=6 block#=515339 blocks=1 obj#=101364 tim=36791549614941
WAIT #3: nam='db file sequential read' ela= 5863 file#=6 block#=515497 blocks=1 obj#=101364 tim=36791549620845
WAIT #3: nam='db file sequential read' ela= 7795 file#=6 block#=515575 blocks=1 obj#=101364 tim=36791549628697
WAIT #3: nam='db file sequential read' ela= 3924 file#=6 block#=567651 blocks=1 obj#=101364 tim=36791549632656
WAIT #3: nam='db file sequential read' ela= 3900 file#=6 block#=567932 blocks=1 obj#=101364 tim=36791549636594
WAIT #3: nam='db file sequential read' ela= 3906 file#=6 block#=568266 blocks=1 obj#=101364 tim=36791549640535
WAIT #3: nam='db file sequential read' ela= 18649 file#=8 block#=748112 blocks=1 obj#=101364 tim=36791549659259
WAIT #3: nam='db file sequential read' ela= 4861 file#=5 block#=2230774 blocks=1 obj#=101364 tim=36791549664171
FETCH #3:c=0,e=53396,p=8,cr=15,cu=0,mis=0,r=15,dep=0,og=1,tim=36791549664212
......

#n n = number of cursor
c cpu time
e elapsed time
p physical reads
cr consistant reads
cu current mode reads
mis miss in cache (?)
r rows processed
dep recursive depth
og optimizer goal
tim time

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/95530/viewspace-1030738/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/95530/viewspace-1030738/