dbhelper 的 博客

xjwyq28@hotmail.com 原创 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) 或是自定义函数， 根据函数处理后的列值-----> 不能判断 最初的<列> 是否为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(列)出现时, 要看一下这对业务来说是否真的有必要, 如果只是程序员的一个书写习惯，不要轻易的忽视它。

xjwyq28@hotmail.com 原创 qq: 123614791