temp_link_id也许有20k. 下面在新refresh的数据库上,计划由原来的nested-loop变成MERGE JOIN 了, 然后新的计划后,要跑20小时才能跑得完.

SELECT i.item_id
FROM item i,
item_link il
WHERE i.item_id = il.item_id
AND latest_ind = 'Y'
AND account_code = 'CRES_20000'
AND account_type_code = 'TRADE'
AND link_id IN
(SELECT link_id
FROM temp_link_id);

Execution plan:
SQL> SELECT i.item_id
2 FROM item i,
3 item_link il
4 WHERE i.item_id = il.item_id
5 AND latest_ind = 'Y'
6 AND account_code = 'CRES_20000'
7 AND account_type_code = 'TRADE'
8 AND link_id IN
9 (SELECT link_id
10 FROM temp_link_id);
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=316 Card=1 Bytes=62)
1 0 NESTED LOOPS (Cost=316 Card=1 Bytes=62)
2 1 MERGE JOIN (CARTESIAN) (Cost=315 Card=1 Bytes=36)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=315 Card
=15655 Bytes=360065)
4 3 INDEX (RANGE SCAN) OF 'IX_ITEM_6' (NON-UNIQUE) (Cost
=35 Card=15655)
5 2 BUFFER (SORT)6 5 SORT (UNIQUE)
7 6 INDEX (FULL SCAN) OF 'IX_TEMP_LINK_ID_1' (NON-UNIQUE)
8 1 INDEX (UNIQUE SCAN) OF 'IX_ITEM_LINK_1' (UNIQUE) (Cost=1
Card=1 Bytes=26)

Changed to below: (加提示,让走nested-loop,问题解决)

SQL> SELECT /*+ ordered use_nl(tl,il,i) index(il, pk_item_link) */ i
.item_id
2 FROM
3 temp_link_id tl,
4 item_link il,
5 item i
6 WHERE
7 il.link_id = tl.link_id
8 AND i.item_id = il.item_id
9 AND i.latest_ind = 'Y'
10 AND i.account_code = 'CRES_20000'
11 AND i.account_type_code = 'TRADE';

no rows selected

Elapsed: 00:00:59.28

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=228994 Card=1 Bytes=
62)
1 0 NESTED LOOPS (Cost=228994 Card=1 Bytes=62)
2 1 NESTED LOOPS (Cost=228992 Card=1 Bytes=39)
3 2 INDEX (FULL SCAN) OF 'IX_TEMP_LINK_ID_1' (NON-UNIQUE)
4 2 INDEX (RANGE SCAN) OF 'PK_ITEM_LINK' (UNIQUE) (Cost=22
8991 Card=11983281 Bytes=311565306)
5 1 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=2 Card=1 B
ytes=23)
6 5 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Card
=1)