[Oracle性能模式] 查找某个号码是否在号码段列表中之性能模式(2011.04.30)
【应用场景】
表test每行保存一个号码段(hm0, hm1)，不同记录的号码段不会出现交叉。需高效地判断出某个号码是否出现在某个号码段中。

-- 建测试表
create table test (
hm0 number(8) not null, -- 号码起
hm1 number(8) not null -- 号码止
);

-- 插入2万条数据
begin
for i in 1..20000 loop
insert into test values(i*100, i*100+10); -- hm0间隔100，每段10个号码，故不会交叉
end loop;
end;
/
commit;

-- 建索引
create index i_test_1 on test(hm0, hm1);
【低效模式】
-- 查找号码 2000006 是否在号码段列表中
select * from test a
where a.hm0 <= 2000006 and a.hm1>= 2000006
;
HM0 HM1
---------- ----------
2000000 2000010

-- 注意查询计划中的 Search columns=1; 低效的原因在于Oracle会判断 hm0 <= 2000006 的每条记录是否满足全部where条件
SELECT STATEMENT, GOAL = CHOOSE
INDEX RANGE SCAN Object owner=SCOTT Object name=I_TEST_1 Search columns=1

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets -- 记录数增多时，该值会加大

【重构方案】
-- 前提：
-- 1. 需在 hm0 上建一 desc 索引
-- 2. 利用 rownum<... 特性,让Oracle找到第一条记录即停止扫描
create index i_test_2 on test(hm0 desc);
-- 利用表本身语义决定的号码段不会交叉的特性，这些Oracle优化器是不会知道的; index hint似乎不能少,否则Oracle不会自动走这个索引
select * from(
select /*+ index(a i_test_2) */ * from test a
where a.hm0 <= 2000006
order by a.hm0 desc) b
where rownum<2 and b.hm1 >= 2000006;

HM0 HM1
---------- ----------
2000000 2000010

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=26)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=3 Card=1 Bytes=26)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1
Bytes=26)

4 3 INDEX (RANGE SCAN) OF 'I_TEST_2' (NON-UNIQUE) (Cost=
2 Card=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets

-- 结束测试删表
drop table test;

perl5原创@20110430