-- Oracle 9.2.0
test@GZSERVER>
test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='9.2.0'
2 /
会话已更改。
test@GZSERVER> select count(*) from outer_large_t where id in (select id from inner_large_t)
2 /
COUNT(*)
----------
20001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=152 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=152 Card=50000 Bytes=1300000)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=11 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=19 Card=70001 Bytes=910013)
test@GZSERVER> select count(*) from outer_large_t t1
2 where exists (select null from inner_large_t t2 where t1.id = t2.id)
3 /
COUNT(*)
----------
20001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=152 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=152 Card=50000 Bytes=1300000)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=11 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=19 Card=70001 Bytes=910013)
test@GZSERVER> select count(*) from outer_small_t where id in (select id from inner_large_t)
2 /
COUNT(*)
----------
1001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=31 Card=1001 Bytes=26026)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=1001 Bytes=13013)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=19 Card=70001 Bytes=910013)
test@GZSERVER> select count(*) from outer_small_t t1
2 where exists (select null from inner_large_t t2 where t1.id = t2.id)
3 /
COUNT(*)
----------
1001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=31 Card=1001 Bytes=26026)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=1001 Bytes=13013)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=19 Card=70001 Bytes=910013)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/6906/viewspace-21642/ ，如需转载，请注明出处，否则将追究法律责任。
