一次隱藏較隱蔽的SQL最佳化問題----不要輕易的忽視count(列)

原創 qq: 123614791

1. 場景說明:

stockpile ----IC卡的供求資訊
xh-- 型號-----字元型

xh 列是 可為null的

index SP_XH, 是函式index, 定義如下create index SP_XH on stockpile (upper(xh));

注意:

( xh 列是 可為null的，會多讀一次表

如果xh上帶not null 的約束，只讀index 不讀表 )

高頻度查詢SQL
select /*+ INDEX(s SP_XH) */ count(s.xh)
from stockpile s
where upper(s.xh) like 'TL431%'

COUNT(S.XH)
-----------
186875

2. 問題出現:

讀完index SP_XH 統計出數量後, 為何有多讀一次STOCKPILE, 幸好它對效能的惡化程度還不算非常厲害，

select /*+ INDEX(s SP_XH) */ count(s.xh)
from stockpile s
where upper(s.xh) like 'TL431%'

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=272 Card=1Bytes=14)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'STOCKPILE' (Cost=272 Card=284 Bytes=3976)
3 2 INDEX (RANGE SCAN) OF 'SP_XH' (NON-UNIQUE) (Cost=3 Card=284)

3. 分析解決:

a. 這個sql如果用count(*), 只讀 index SP_XH { upper(s.xh) } , 不讀表 。

b. 如果用 count(s.xh) , s.xh的資料只有在表中有 , 對應的index SP_XH { upper(s.xh) } 沒有, 為排除s.xh空值影響, 所以讀完index還要讀表。

註解： 與 某 function(列) 之間, ORACLE不能夠 根據 其中一個判斷另一個是否為NULL, 例如 function 是NVL(列, '1'), DECODE(列, NULL, '1', '2',NULL) 或是自定義函式， 根據函式處理後的列值-----&gt 不能判斷 最初的 是否為NULL。

c. 如改寫成 count( upper(s.xh)) 一樣可以只讀index不讀表。

select /*+ INDEX(s SP_XH) */ count( upper(s.xh))
from stockpile s
where upper(s.xh) like 'TL431%'

4. 回顧:

a. 函式index 中 存貯的是: 函式處理列後的值 和 行rowid。

b. 當xh 列是 可為null的，count(列) 出現, 走的又是 列的函式index, count(列)會多讀一次表。

c. 當xh列上帶not null 的約束, 只讀index不讀表。

d. count(列)出現時, 要看一下這對業務來說是否真的有必要, 如果只是程式設計師的一個書寫習慣，不要輕易的忽視它。

原創 qq: 123614791

[@more@]