[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