oracle 强制走hash,求救啊！SQL走HASH JOIN ，却不走NESTED 导致执行速度很慢

SQL 如下：

sql: SELECT b.pallet_no

FROM sajet.g_sn_status a, sajet.g_pack_pallet b

WHERE a.model_id = :b2

AND a.pallet_no = b.pallet_no

AND b.terminal_id = :b1

AND b.close_flag = 'N'

AND ROWNUM = 1

problem: a table have 20000000 records b table have 42155 records

using hash join ,query very slow but using nested loop get result very fast

.

ISSUE VERIFICATION

===================

SQL> SELECT b.pallet_no

2 FROM sajet.g_sn_status a, sajet.g_pack_pallet b

3 WHERE a.model_id = '1000083350'

4 AND a.pallet_no = b.pallet_no

5 AND b.terminal_id = '10000977'

6 AND b.close_flag = 'N'

7 and rownum =1 ;

no rows selected

Elapsed: 00:00:47.37

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3177 Card=1 Bytes=35)

1 0 COUNT (STOPKEY)

2 1 HASH JOIN (Cost=3177 Card=668 Bytes=23380)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'G_PACK_PALLET' (Cost=10 Card=231 Bytes=4158)

4 3 INDEX (RANGE SCAN) OF 'G_PACK_PALLET_TERMINAL_IDX' (NON-UNIQUE) (Cost=1 Card=463)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'G_SN_STATUS' (Cost=3166 Card=87921 Bytes=14

94657)

6 5 INDEX (RANGE SCAN) OF 'G_SN_STATUS_MODEL_IDX' (NON-UNIQUE) (Cost=413 Card=87921)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

19478 consistent gets

7751 physical reads

0 redo size

225 bytes sent via SQL*Net to client

362 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> SELECT /*+USE_NL(b,a)*/ b.pallet_no

2 FROM sajet.g_sn_status a, sajet.g_pack_pallet b

3 WHERE a.model_id = '1000083350'

4 AND a.pallet_no = b.pallet_no

5 AND b.terminal_id = '10000977'

6 AND b.close_flag = 'N'

7 and rownum =1 ;

no rows selected

Elapsed: 00:00:01.65

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=163327 Card=1 Bytes=35)

1 0 COUNT (STOPKEY)

2 1 NESTED LOOPS (Cost=163327 Card=668 Bytes=23380)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'G_PACK_PALLET' (Cost=10 Card=231 Bytes=4158)

4 3 INDEX (RANGE SCAN) OF 'G_PACK_PALLET_TERMINAL_IDX' (NON-UNIQUE) (Cost=1 Card=463)

5 2 AND-EQUAL (Cost=413 Card=3 Bytes=51)

6 5 INDEX (RANGE SCAN) OF 'G_SN_STATUS_PALLET' (NON-UNIQUE) (Cost=413 Card=87921)

7 5 INDEX (RANGE SCAN) OF 'G_SN_STATUS_MODEL_IDX' (NON-UNIQUE) (Cost=412 Card=87921)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

103 consistent gets

89 physical reads

0 redo size

225 bytes sent via SQL*Net to client

362 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed