[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