Table: dbphr.p5004_phrorderreviseque
PK & index: Pharmacycode & quetype & revisedatechar & reviseno四個欄位建成一個PK(index)
(也就是4個欄位建成一個index)
Case1: 系統目前所RUN的SQL:
/* Formatted on 2008/09/15 15:39 (Formatter Plus v 4.8.8 ) */
SELECT *
FROM dbphr.p5004_phrorderreviseque
WHERE pharmacycode = :"SYS_B_0"
| Plan | |
| SELECT STATEMENT CHOOSE Cost: 144 | |
|
| 1 TABLE ACCESS FULL DBPHR.P5004_PHRORDERREVISEQUE Cost: 144 Bytes: 1,868,124 Cardinality: 45,564 |
條件中只寫出PK中的第一個欄位,結果走的是Full table scan.
Case2:
SELECT *
FROM dbphr.p5004_phrorderreviseque
WHERE pharmacycode = :"SYS_B_0"
AND quetype = :"SYS_B_1"
AND revisedatechar = :"SYS_B_2"
AND reviseno = :"SYS_B_0"
| Plan | ||
| SELECT STATEMENT CHOOSE Cost: 3 Bytes: 41 Cardinality: 1 | ||
|
| 2 TABLE ACCESS BY INDEX ROWID DBPHR.P5004_PHRORDERREVISEQUE Cost: 3 Bytes: 41 Cardinality: 1 | |
|
|
| 1 INDEX UNIQUE SCAN UNIQUE DBPHR.PK_P5004 Cost: 2 Cardinality: 1 |
全部寫出來~就會走index.
Case3:
SELECT *
FROM dbphr.p5004_phrorderreviseque
WHERE pharmacycode = :"SYS_B_0"
AND quetype = :"SYS_B_1"
| Plan | |
| SELECT STATEMENT CHOOSE Cost: 287 Bytes: 60,557 Cardinality: 1,477 | |
|
| 1 TABLE ACCESS FULL DBPHR.P5004_PHRORDERREVISEQUE Cost: 287 Bytes: 60,557 Cardinality: 1,477 |
Where 條件有前兩個欄位,還是走full table scan
Case4:
SELECT *
FROM dbphr.p5004_phrorderreviseque
WHERE pharmacycode = :"SYS_B_0"
AND quetype = :"SYS_B_1"
AND revisedatechar = :"SYS_B_2"
| Plan | ||
| SELECT STATEMENT CHOOSE Cost: 111 Bytes: 5,043 Cardinality: 123 | ||
|
| 2 TABLE ACCESS BY INDEX ROWID DBPHR.P5004_PHRORDERREVISEQUE Cost: 111 Bytes: 5,043 Cardinality: 123 | |
|
|
| 1 INDEX RANGE SCAN UNIQUE DBPHR.PK_P5004 Cost: 4 Cardinality: 123 |
Where 條件有前三個欄位, oracle抉擇後選擇走index,
但請跟case 2的cost比較(cost不同,表示是兩個不同的執行計畫)
Case5:
SELECT *
FROM dbphr.p5004_phrorderreviseque
WHERE quetype = :"SYS_B_0"
AND revisedatechar = :"SYS_B_1"
AND reviseno = :"SYS_B_2"
AND pharmacycode = :"SYS_B_0"
| Plan | ||
| SELECT STATEMENT CHOOSE Cost: 3 Bytes: 41 Cardinality: 1 | ||
|
| 2 TABLE ACCESS BY INDEX ROWID DBPHR.P5004_PHRORDERREVISEQUE Cost: 3 Bytes: 41 Cardinality: 1 | |
|
|
| 1 INDEX UNIQUE SCAN UNIQUE DBPHR.PK_P5004 Cost: 2 Cardinality: 1 |
將四個條件寫上,並給它亂調一下,
我們發現它還是走index,這是因為在oracle收到sql命令後內部會改寫成比較恰當的SQL敘述
(Oracle會自動有調整順序如同Case 2之敘述, 所以cost是一樣的),
所以我們認為只要有將相關的欄位寫出來,無論甚麼順序,SQL都會走index.
Case6:
SELECT *
FROM dbphr.p5004_phrorderreviseque
WHERE --quetype = :"SYS_B_0"
--AND revisedatechar = :"SYS_B_1"
--AND reviseno = :"SYS_B_2"
--AND
pharmacycode = :"SYS_B_0"
| Plan | |
| SELECT STATEMENT CHOOSE Cost: 287 Bytes: 60,557 Cardinality: 1,477 | |
|
| 1 TABLE ACCESS FULL DBPHR.P5004_PHRORDERREVISEQUE Cost: 287 Bytes: 60,557 Cardinality: 1,477 |
回到Case1,確定有使用到PK中的第一欄位, 加入hint: /*+ rule */ 強迫它走index
SELECT /*+ rule */ *
FROM dbphr.p5004_phrorderreviseque
WHERE pharmacycode = :"SYS_B_0"
| Plan | ||
| SELECT STATEMENT HINT: RULE | ||
|
| 2 TABLE ACCESS BY INDEX ROWID DBPHR.P5004_PHRORDERREVISEQUE | |
|
|
| 1 INDEX RANGE SCAN UNIQUE DBPHR.PK_P5004 |
所以結論:
目前我們ntuh系統中,有很多是多個欄位建成一個pk(也相對的有多欄位建成一個index)時,
如果where條件中無法將全部欄位寫出,
至少必須寫出第一個欄位並使用/*+ rule */ hint來強迫Oracle使用index來查詢.
(要不然要不要走index,全由Oracle自己的選擇)
留言列表
資訊技術 (11)
