Oracle 資料庫最佳化

標籤：col   code   轉換   center   臨時   資訊管理   rebuild   lead   segment   

 

 

 

 

 

Oracle 資料庫最佳化

 

 

 

 

 

 

資訊管理部

 

 

目錄

一、 SELECT查詢語句中避免使用 ‘*’.................................................... 2

二、 減少資料庫訪問次數：............................................................................. 2

三、 查詢單條記錄............................................................................................... 2

四、 選擇最優表名順序：................................................................................. 2

五、 WHERE子句中的串連............................................................................... 3

六、 使用DECODE函數可以避免重複掃描相同記錄或重複串連相同的表    3

七、 刪除全表操作推薦使用TRUNCATE不建議使用DELETE............. 3

八、 盡量多使用COMMIT：............................................................................ 4

九、 減少對錶的查詢：...................................................................................... 5

十、 通過內建函式提高SQL效率.：............................................................ 5

十一、 使用表的別名(Alias)：.......................................................................... 5

十二、 對常用查詢條件設定索引................................................................... 5

十三、 sql語句用大寫的；因為oracle總是先解析sql語句，把小寫字母轉換成大寫的再執行.................................................................................................................... 6

十四、 在java代碼中盡量少用串連符“＋”連接字串！............... 6

十五、 避免在索引列上使用NOT.................................................................. 6

十六、 避免在索引上使用計算........................................................................ 6

十七、 用>=替代>............................................................................................... 6

十八、 用UNION替換OR (適用於索引列)................................................ 6

十九、 普通查詢中用IN來替換OR.............................................................. 7

二十、 避免在索引列上使用IS NULL和IS NOT NULL.......................... 7

二十一、 總是使用索引的第一個列：.......................................................... 7

二十二、 用UNION-ALL 替換UNION ( 如果有可能的話)：............. 7

二十三、 避免改變索引列的類型.:................................................................. 7

二十四、 某些WHERE子句不使用索引....................................................... 7

二十五、 避免使用耗費資源的操作:.............................................................. 8

二十六、 最佳化GROUP BY.................................................................................. 8

二十七、 .................................................................................................................. 8

 

 

 

一、SELECT查詢語句中避免使用 ‘*’

二、減少資料庫訪問次數：

      減少資料庫IO操作壓力

三、查詢單條記錄

四、選擇最優表名順序：

       Oracle解析器解析規則從右向左的順序處理From子句表名，此時宜將記錄條數最少的表或者交叉表（被其他引用的表）作為基礎資料表（From子句中寫最後的表）

五、WHERE子句中的串連

Oracle解析器解析WHERE子句採用從下到上的順序解析，過濾大量資料的篩選條件推薦寫在WHERE子句最後

六、使用DECODE函數可以避免重複掃描相同記錄或重複串連相同的表

七、刪除全表操作推薦使用TRUNCATE不建議使用DELETE

TRUNCATE將會徹底刪除資料不可恢複，消耗資源小於DELETE。

① 在功能上，truncate是清空一個表的內容，它相當於delete from table_name

② delete是dml操作，truncate是ddl操作；因此，用delete刪除整個表的資料時，會產生大量的roolback(復原)，佔用很多的rollback segments(復原段)， 而truncate不會

③ 在記憶體中，用delete刪除資料，資料表空間中其被刪除資料的表佔用的空間還在，便於以後的使用，另外它是“假相”的刪除，相當於windows中用delete刪除資料是把資料放到資源回收筒中，還可以恢複，當然如果這個時候重新啟動系統（OS或者RDBMS），它也就不能恢複了！

而用truncate清除資料，記憶體中資料表空間中其被刪除資料的表佔用的空間會被立即釋放，相當於windows中用shift+delete刪除資料，不能夠恢複！

④ truncate 調整high water mark 而delete不；truncate之後，TABLE的HWM退回到 INITIAL和NEXT的位置（預設）delete則不可以。

⑤truncate只能對TABLE，delete 可以是table,view,synonym

⑥TRUNCATETABLE 的對象必須是本模式下的，或者有drop any table的許可權 而 DELETE 則是對象必須是本模式下的，或被授予 DELETE ONSCHEMA.TABLE 或DELETE ANY TABLE的許可權

⑦在外層中，truncate或者delete後，其佔用的空間都將釋放

⑧truncate和delete只刪除資料，而drop則刪除整個表（結構和資料）

小技巧：在刪除大資料量時(一個表中大部分資料時),

先將不需要刪除的資料複製到一個暫存資料表中；

trunc table 表；

將不需要刪除的資料複製回來。

八、盡量多使用COMMIT：

儘可能使用COMMIT需求釋放的資源而減少:

COMMIT所釋放的資源:

a. 復原段上用於恢複資料的資訊.

b. 被程式語句獲得的鎖

c. redo logbuffer 中的空間

d. ORACLE為管理上述3種資源中的內部花費

九、減少對錶的查詢：

在含有子查詢的SQL語句中,要特別注意減少對錶的查詢..

十、 通過內建函式提高SQL效率.：

複雜的SQL往往犧牲了執行效率. 更傾向於運用函數解決問題

十一、使用表的別名(Alias)：

當在SQL語句中串連多個表時, 請使用表的別名並把別名首碼於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.

十二、對常用查詢條件設定索引

索引是表的一個概念部分,用來提高檢索資料的效率，ORACLE使用了一個複雜的自平衡B-tree結構. 通常,通過索引查詢資料比全表掃描要快.當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE最佳化器將使用索引. 同樣在連接多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primarykey)的唯一性驗證.。那些LONG或LONG RAW資料類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效.當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢.。週期性重構索引是有必要的：

ALTER INDEX<INDEXNAME> REBUILD <TABLESPACENAME>

十三、sql語句用大寫的；因為oracle總是先解析sql語句，把小寫字母轉換成大寫的再執行

十四、在java代碼中盡量少用串連符“＋”連接字串！

十五、避免在索引列上使用NOT 　

NOT會產生在和在索引列上使用函數相同的影響. 當ORACLE”遇到”NOT,他就會停止使用索引轉而執行全表掃描.

十六、避免在索引上使用計算

索引列是函數的一部分．最佳化器將不使用索引而使用全表掃描．

十七、用>=替代>

資料記錄處理機制問題

十八、用UNION替換OR (適用於索引列)

用UNION替換WHERE子句中的OR將會起到較好的效果.對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低

十九、普通查詢中用IN來替換OR

二十、避免在索引列上使用ISNULL和IS NOT NULL

盡量避免在欄位資訊中出現空值，在索引中出現空值欄位會導致ORACLE無法使用該索引

二十一、總是使用索引的第一個列：

如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,最佳化器才會選擇使用該索引. 這也是一條簡單而重要的規則，當僅引用索引的第二個列時,最佳化器使用了全表掃描而忽略了索引

二十二、 用UNION-ALL 替換UNION ( 如果有可能的話)：

當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION,這樣排序就不是必要了

UNION ALL 將重複輸出兩個結果集合中相同記錄，所以請根據具體需求使用。

二十三、避免改變索引列的類型.:

當比較不同資料類型的資料時, ORACLE自動對列進行簡單的類型轉換. .

二十四、某些WHERE子句不使用索引

(1)‘!=‘ 將不使用索引. 記住, 索引只能告訴你什麼存在於表中,而不能告訴你什麼不存在於表中.

(2)‘||‘是字元串連函數. 就象其他函數那樣, 停用了索引.

(3)‘+‘是數學函數. 就象其他數學函數那樣, 停用了索引.

(4)相同的索引列不能互相比較,這將會啟用全表掃描.

二十五、 避免使用耗費資源的操作:

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL語句會啟動SQL引擎

執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 通常, 帶有UNION, MINUS, INTERSECT的SQL語句都可以用其他方式重寫.

二十六、 最佳化GROUP BY

提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉

Oracle 資料庫最佳化