數據庫版本oracle 9.2.0.8i
Oracle只需要按照表在From子句中的出现顺序进行连接,从而按照我们的意图进行解析或执行.
SELECT b.ID Pallet_ID, b.No Pallet_No, b.Is_Closed, b.Pack_Mode
FROM DMPSFIS1.R_Pallet_Detail a, DMPSFIS1.R_Pallet b
WHERE a.Carton_ID = 157709265
AND a.Del_Flag = 0
AND b.Pack_Mode = '1'
AND b.Del_Flag = 0
AND b.ID = a.Pallet_ID;
執行計劃
0 SELECT STATEMENT Optimizer Mode=RULE
1 0 NESTED LOOPS
2 1 TABLE ACCESS BY INDEX ROWID DMPSFIS1.R_PALLET
3 2 INDEX RANGE SCAN DMPSFIS1.IX
4 1 TABLE ACCESS BY INDEX ROWID DMPSFIS1.R_PALLET_DETAIL
5 4 AND-EQUAL
6 5 INDEX RANGE SCAN DMPSFIS1.IX_R_PATTET_DETIAL_CARTON_ID
7 5 INDEX RANGE SCAN DMPSFIS1.IX_R_PATTET_DETAIL_PATTET_ID
看這條件只有1筆數據。
SELECT count(1)
FROM DMPSFIS1.R_Pallet_Detail a
WHERE a.Carton_ID = 157709265
AND a.Del_Flag = 0
加提示后的執行計劃,1s可以跑出結果。
set autotrace trace explain
SELECT /*+ordered */ b.ID Pallet_ID, b.No Pallet_No, b.Is_Closed, b.Pack_Mode
FROM DMPSFIS1.R_Pallet_Detail a, DMPSFIS1.R_Pallet b
WHERE a.Carton_ID = 157709265
AND a.Del_Flag = 0
AND b.Pack_Mode = '1'
AND b.Del_Flag = 0
AND b.ID = a.Pallet_ID;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=RULE (Cost=5 Card=1 Bytes=32)
1 0 NESTED LOOPS (Cost=5 Card=1 Bytes=32)
2 1 TABLE ACCESS BY INDEX ROWID DMPSFIS1.R_PALLET_DETAIL (Cost=4 Card=1 Bytes=11)
3 2 INDEX RANGE SCAN DMPSFIS1.IX_R_PATTET_DETIAL_CARTON_ID (Cost=3 Card=1)
4 1 TABLE ACCESS BY INDEX ROWID DMPSFIS1.R_PALLET (Cost=1 Card=1 Bytes=21)
5 4 INDEX UNIQUE SCAN DMPSFIS1.IX_R_PALLET_ID (Card=1)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23757700/viewspace-730616/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23757700/viewspace-730616/