都说用exists比in好，可是最近一个查询用exists
crkd 及crkd_mx 表的 djbm都有索引
selectdjbm,xh,spbm,spmc,spgg,jldw,sl,dj,je,bhsdj,bhsje,slv,se
from crkd_mx where exists (select 1 from crkd a,crkd_xs b
where a.djbm=b.djbm and a.djbm=crkd_mx.djbmand rq = to_date('20040730','yyyymmdd') )
未选定行
已用时间:00: 02: 34.04
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 FILTER
21 TABLE ACCESS (FULL) OF 'CRKD_MX'
31 NESTED LOOPS
43 TABLE ACCESS (BY INDEX ROWID) OF 'CRKD'
54 INDEX (UNIQUE SCAN) OF 'PK_CRKD' (UNIQUE)
63 INDEX (UNIQUE SCAN) OF 'PK_CRKD_XS' (UNIQUE)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
3747064consistent gets
112509physical reads
0redo size
885bytes sent via SQL*Net to client
372bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed
最后俺改成比较土的in方式
selectdjbm,xh,spbm,spmc,spgg,jldw,sl,dj,je,bhsdj,bhsje,slv,se
from yw_crkd_mx where djbm in (select a.djbm from yw_crkd a,yw_crkd_xs b
where a.djbm=b.djbmand ywrq = to_date('20040727','yyyymmdd'))
未选定行
已用时间:00: 00: 00.01
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (BY INDEX ROWID) OF 'CRKD_MX'
21 NESTED LOOPS
32 VIEW OF 'VW_NSO_1'
43 SORT (UNIQUE)
54 NESTED LOOPS
65
TABLE ACCESS (BY INDEX ROWID) OF 'CRKD'
76
INDEX (RANGE SCAN) OF 'INDX_CRKD_YWRQ' (NON
-UNIQUE)
85
INDEX (UNIQUE SCAN) OF 'PK_CRKD_XS' (UNIQUE)
92 INDEX (RANGE SCAN) OF 'RELATIONSHIP_111_FK' (NON-UNIQU
E)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
3consistent gets
0physical reads
0redo size
885bytes sent via SQL*Net to client
372bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
0rows processed
这是怎么回事，怎么解释呢，还有 执行计划中的 view 'VW_NSO_1' 是什么东西啊，我不记得有这个试图阿
|
