Oracle SQL最佳化一（常見方法）

標籤：使用   io   strong   資料   ar   2014   art   div   

1、表訪問方式最佳化：a)普通表優先“Index Lookup 索引掃描”，避免全表掃描

大多數情境下，通過“Index Lookup 索引掃描”要比“Full Table Scan (FTS) 全表掃描”效率要高的多。在編寫SQL時，為了保證查詢能夠使用索引，需要避免出現如下情境:

• is null 和 is not null

在oracle中null是不能夠作為索引的，如果某列資料中有“null”，不要在該列上建立索引，即使建立，也不會提高查詢效能。

而在SQL語句中，如果使用is null和is not null，oracle的SQL最佳化器是不允許走索引的。

• 前置萬用字元

在使用like時，出現前置萬用字元，例如

 

oracle11代碼SELECT *
FROM t_sys_conf_midtoresult t
WHERE t.targettable LIKE ‘%mdsp_rpt%‘

 

此時，在targettable上的索引不會生效。

萬用字元包括‘%‘和‘_‘。

• not

not會導致查詢索引不生效。

not的形式有如下幾種：

not (fee = 0)

fee <> 0

fee != 0

 

如果該欄位上必須要使用索引，請修改為下列表達方式：

fee > 0 and fee < 0

 

• 盡量將運算式放在=號右邊

在欄位上使用運算式，會導致索引不生效。例如：

 

oracle11代碼SELECT *
FROM t_mdsp_rpt_product_revenue t
WHERE TO_CHAR(t.dtime, ‘yyyymmdd‘) = ‘20140701‘

 

一般建議，將運算式放到=號右邊，左邊欄位名稱上不要添加任何錶達式。例如：

 

oracle11代碼SELECT *
FROM t_mdsp_rpt_product_revenue t
WHERE t.dtime = TO_DATE(‘20140701‘,‘yyyymmdd‘)

 

• 避免資料類型的隱式轉換

在SQL語句中，常見的隱式轉換如下：

字元型和數值型比較，Oracle會將字元型隱式轉換成數值型；

字元型和date比較，oracle會將字元型隱式轉換成date；

 

隱式轉換如果發生在欄位上，會造成索引失效。原理同“欄位上加運算式”

 

• 注意：若避免了上述情況，仍然不走索引，不可輕易的使用hint方式強制走索引，需要根據實際情況具體分析。

b)分區表必須要通過分區鍵檢索部分分區，不可全表掃描

在解釋計劃中，對於分區表的掃描，不可為“partition range all”。

where條件中，一定要加入對分區鍵列的條件過濾，同樣要避免欄位上加運算式和隱式轉換

• 對於list分區

一般使用=判斷，例如：

 

oracle11代碼 WHERE dtime = ‘20140701‘

 

• 對於range分區

一般使用範圍判斷，例如between ... and ...或者>、<、>=、<=等

c)分區表上的索引一般使用本地索引

需要注意的原則與普通表上的索引一致。

在查詢分區表時，Oracle先通過分區鍵找到需要查詢的分區，在分區內部使用本地索引進行檢索。

2、表串連方式最佳化a)NESTED LOOPS（嵌套迴圈NL）最佳化，在內部表關聯欄位上建立索引

最常見的表串連方式，串連原理如下：

從外部表格（驅動表）中擷取第一條資料，去內部表中查詢匹配，若匹配到則輸出。

取第二條資料匹配，直到外部表格資料遍曆一遍。

在CBO下，Oracle自動選擇資料量較小的表（或者子查詢結果集）作為內部表。

在內部表上建立索引，提高每次檢索的效率。

b)Hash Join

在絕大多數情境下，Hash Join是效率最高的串連。

一般用於大表和小表的串連，在小表資料能夠全部放到記憶體中時，效率最高。

c)排序合并串連(Sort Merge Join (SMJ))

它的出現必然伴隨著排序操作，效率較低，在Oracle 10g版本以後基本不會出現，若出現，需要確認是否在子查詢中是否使用了不必要的排序。

d)CARTESIAN JOIN（笛卡爾串連）

這個就不用多說了，必須要避免的。

3、運算方式最佳化

避免出現不必要的sort和sort group by，一般SQL中只在最終輸出結果時進行排序，內部子查詢不排序。