oracle 單列索引 多列索引的效能測試

標籤：style   io   color   使用   sp   strong   資料   on   2014   

清除oralce 緩衝：alter system flush buffer_cache;

環境：oracle 10g 、 400萬條資料，頻率5分鐘一條

1.應用情境：  找出所有網站的最新一條資料。sql語句如下：

——————————————————————————————————————————————————

with aa as(
select t1.EQP_ID ,max(MEASURE_TIME) maxtm from PLU_WATER_DATA t1 where t1.MEASURE_TIME >= to_date(‘2014-01-28 00:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘)
and t1.MEASURE_TIME < to_date(‘2014-01-28 02:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘) group by t1.EQP_ID

)
, bb as
(
select T1.EQP_ID as STCD,
T1.MEASURE_TIME as TM,
T1.FLUX_VALUE as FLOW
from PLU_WATER_DATA T1
where t1.MEASURE_TIME >= to_date(‘2014-01-28 00:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘)
and t1.MEASURE_TIME < to_date(‘2014-01-28 02:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘)

)
select * from aa inner join bb on aa.EQP_ID = bb.stcd and aa.maxtm = bb.tm inner join vi_plustation cc on cc.stcd =aa.EQP_ID

 ——————————————————————————————————————————

執行結果：

a.建立tm+stcd多列索引、tm單列索引、stcd單列索引  （執行時間2秒）
b.建立tm+stcd多列索引、stcd單列索引 （執行時間20秒）
c.建立stcd單列索引 (執行時間N秒)
d.建立tm+stcd多列索引 (執行時間22秒)
e.建立tm+stcd+tn+tp+cod多列索引 (執行時間71秒)
f.建立tm單列索引 (執行時間2秒)

 

2.應用情境  ：  找出單個網站的一段時間內的資料。sql語句如下：

select T1.EQP_ID as STCD,
T1.MEASURE_TIME as TM,
T1.FLUX_VALUE as FLOW
from PLU_WATER_DATA T1
where t1.MEASURE_TIME >= to_date(‘2014-01-28 00:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘)
and t1.MEASURE_TIME < to_date(‘2014-01-28 23:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘) and eqp_id = ‘1244‘

執行結果：

查詢條件在 tm和stcd上
a. 建立tm單列索引 (執行時間17秒）
b. 建立tm單列索引、stcd單列索引 (執行時間 3秒）
c. 建立tm單列索引、stcd單列索引、tm+stcd多列索引 (執行時間 0.5秒）
d. 建立tm+stcd多列索引 (執行時間 0.7秒）

 

3.結論：查詢條件中stcd或者tm是單獨限制where條件，如果使用了stcd+tm的多列索引，效率降低。如果stcd和tm是作為聯合where條件，建立stcd和tm的單列索引，效率明顯低於使用stcd+tm的多列索引。

  a.當一個sql語句有網站分組跟時間查詢嵌套使用時，推薦將stcd和tm分開建立單列索引。列如：

select t1.EQP_ID ,max(MEASURE_TIME) maxtm from PLU_WATER_DATA t1 where t1.MEASURE_TIME >= to_date(‘2014-01-28 00:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘) and t1.MEASURE_TIME < to_date(‘2014-01-28 02:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘) group by t1.EQP_ID)

像這樣的語句如果建立的是 stcd+tm的多列索引，那麼讀取速度是7m，而如果使用stcd和tm分開建立單列索引，速度在2m內。

  b.當一個sql語句 指定“網站”和“時間”時，推薦建立stcd+tm多列索引。例如

select T1.EQP_ID as STCD,T1.MEASURE_TIME as TM,T1.FLUX_VALUE as FLOW from PLU_WATER_DATA T1 
where t1.MEASURE_TIME >= to_date(‘2014-01-28 00:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘)
and t1.MEASURE_TIME < to_date(‘2014-01-28 23:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘) and eqp_id = ‘1244‘

像這樣的語句如果建立的是stcd和tm分開建立單列索引，速度在3m，但如果建立了stcd+tm的多列索引，速度在0.7m

 c.當一個sql語句 制定“時間”查詢時，推薦建立 tm單列索引。例如

select T1.EQP_ID as STCD,T1.MEASURE_TIME as TM,T1.FLUX_VALUE as FLOW from PLU_WATER_DATA T1 
where t1.MEASURE_TIME >= to_date(‘2014-01-28 00:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘)
and t1.MEASURE_TIME < to_date(‘2014-01-28 23:00:00‘, ‘yyyy-MM-dd hh24:mi:ss‘)

像這樣的語句如果建立的是stcd+tm的複合索引，速度在5m，如果建立在tm的單列索引上，速度在1.5m

oracle 單列索引 多列索引的效能測試