SQL優化例項

示例一：hint濫用

select /*+ ordered use_nl(b a c d)*/
       *
  from b,a,c,d
 where b.col1 = a.col1
   and a.col2 = c.col2
   and a.col3 = d.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt <   to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析：
1）表之間的關係：
2）資料量，索引情況   b 200萬，a 200萬 ，過濾後50行，c 200行， d 1000行
3）優化器模式  RBO  or  CBO
4）不同的優化器模式採用不用的優化策略

優化措施：---》
對於CBO，把新增的hint去掉就可以了，Oracle會根據資料量和索引情況生成高效的執行計劃
select *
  from b,a,c,d
 where b.col1 = a.col1
   and a.col2 = c.col2
   and a.col3 = d.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt <   to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

--------------------------------------------------------------

示例二：多餘的表或列訪問

select a.col1,b.col2,a.dt
  from a,c,b
 where a.col1 =  c.col1
   and c.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析：
1）表之間的關係 a,c 1:1  c,b 1:1
2）select裡沒有出現c表的列，此時可以考慮c表是否是必須的
3）如果分析表之間的關係及資料的特點發現c表不是必須的，可以去掉c表的訪問
4）說明：當然也有些情況下c表可能是必須的，具體情況具體分析

優化措施：--》
如果經過分析發現c表的訪問確實是多餘的，那麼上述語句可以改為：
select a.col1,b.col2,a.dt
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

select col1,col2,dt
  from
(
select a.*,b.col2,a.rownum rn
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')
   order by a.col1
)
where rn between 10 and 20

分析：
1）內層查詢使用a.*，而外層只需要a的col1這一列，所以把內層查詢的c.*具體化，減少資源和時間的消耗

優化措施：--》
select col1,col2,dt
  from
(
select a.col1,b.col2,a.rownum rn
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')
   order by a.col1
)
where rn between 10 and 20

--------------------------------------------------------

示例三：索引使用的靈活處理

select a.dt,b.timezone,a.col1,a.col2,a.col3
  from a,b
 where a.col1 = b.col1
   and a.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') - b.timezone
   and a.dt   < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS') -b.timezone
保證同一時間段統計

分析：
1）表之間的關係：
2）資料量，索引情況 a 100萬  b 200    a.dt有索引， a.col1，b.col1有索引
3）條件中a.dt列的比較範圍是變化的，所以導致dt列的索引無法使用
4）分析b.timezone的數值範圍，在-12：00～12：00之間
5）為了使用a.dt列的索引，考慮可以把a.dt的範圍條件放大到固定值，然後再對結果集進行過濾。

優化措施：--》

select *
from
(
select a.dt,b.timezone,a.col1,a.col2,a.col3
  from a,b
 where a.col1 = b.col1
   and a.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') -1
   and a.dt  < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')+1
) ab

   where ab.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') -ab.timezone
   and ab.dt  < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')- ab.timezone

----------------------------------------------------------

示例四：有沒有合適的索引可用

select a.col1,a.col2,a.col3,b.col4,c.col5
  from a,b,c
 where a.col1 = b.col1
   and a.col2 = b.col2
   and a.col3 = c.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析：
1）表之間的關係
2）資料量，索引情況   a.dt, b.col1, b.col2, c.col3列分別有索引
3）但是單獨使用b.col1列的索引或者b.col2列的索引，資料篩選效果都不好
4）這樣考慮為b表的col1,col2建立聯合索引

優化措施：--》
為b表的col1,col2列建立聯合索引

------------------------------------------------------

示例五：不必要的外連線

select a.col1,b.col2,b.col3
  from a,b
 where a.col1 = b.col2(+)
   and b.col3 > 1000

分析：
1）條件b.col3>1000意味著原本使用外連線多出來的列要被排除掉，所以此處外連線是不需要的

優化措施：--》

select a.col1,b.col2,b.col3
  from a,b
 where a.col1 = b.col2
   and b.col3 > 1000

-----------------------------------------------------

示例六：with子句的使用

select ab.col1,ab.col3,c.col2
  from c,
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
) ab
where c.col1 = ab.col1
union
select ab.col1,ab.col3,d.col2
  from d,
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
) ab
where d.col1 = ab.col1

分析：
1）該語句中出現了多處相同的子查詢，可以使用with子句來進行簡化，減少資料訪問，提高效率

優化措施：--》
with ab as
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
)
select ab.col1,ab.col3,c.col2
  from c,ab
where c.col1 = ab.col1
union
select ab.col1,ab.col3,d.col2
  from d,ab
where d.col1 = ab.col1

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-2125872/，如需轉載，請註明出處，否則將追究法律責任。