oracle SQL優化規則有哪些

這篇文章主要講解了“oracle SQL優化規則有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“oracle SQL優化規則有哪些”吧！

1>選擇最有效率的表名順序：

1.把記錄少的表放在from子句的最后面一個表.

2.如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.

原因：ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎表driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行排序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.

2> WHERE子句中的連接順序 ：

ORACLE采用自右向左的順序解析WHERE子句,, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.

3.>SELECT子句中避免使用 ‘* ‘

ORACLE在解析的過程中, 需要通過查詢數據字典將'*' 依次轉換成所有的列名.

4.> 使用表的別名(Alias)

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

(Column歧義指的是由于SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)

5.>減少訪問的次數:

當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量, 讀數據塊等等. 由此可見, 減少訪問數據庫的次數, 就能實際上減少ORACLE的工作量.

6.>(可能的話)用TRUNCATE替代DELETE.

當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有COMMIT事務,ORACLE會將數據恢復到執行刪除命令之前的狀況.

而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行后,數據不能被恢復.因此很少的資源被調用,執行時間也會很短.

(TRUNCATE只在刪除全表里的記錄時適用,TRUNCATE是DDL不是DML)

7.> (可能的話)使用COMMIT

只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:

COMMIT所釋放的資源:

a. 回滾段上用于恢復數據的信息.

b. 被程序語句獲得的鎖

c. redo log buffer 中的空間

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

8.>(可能的話)用Where子句替換HAVING子句

盡量少使用HAVING子句, HAVING 只會在檢索出所有記錄之后才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.

9.>(某些情況下)可以用EXISTS替代IN . NOT EXISTS替代NOT IN

性能比較:

1.Select * from t1 where x in ( select y from t2)

2.select * from t1 where

exists ( select 1 from t2 where t2. y = t1.x )

當t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當子查詢記錄集很小時用in比較合適.

原因分析：

1.Select * from T1 where x in ( select y from T2 )

執行的過程相當于:

select *   from t1, ( select distinct y from t2 ) t3 where t1.x = t3.y;

2. select * from t1 where exists ( select 1 from t2 where t2.y = t1.x )

執行的過程相當于:

for x in ( select * from t1 ) loop

      if ( exists ( select 1 from t2 where t2.y = t1.x )

      then

         OUTPUT THE RECORD

      end if

end loop

這樣表T1 要被完全掃描一遍.

所以可以得出結論:當t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當子查詢記錄集很小時用in比較合適.

10.>用表連接替換EXISTS

改進第9打優化規則的例子.

11.>用EXISTS替換DISTINCT

EXISTS 使查詢更為迅速,因為RDBMS核心模塊在子查詢的條件一旦滿足后

立刻返回結果. DISTINCT會先進行排序,然后會根據排序后的順序去除相同的行.

12.>使用顯式的游標(CURSOR)

使用隱式的游標,將會執行兩次操作. 第一次檢索記錄, 第二次檢查TOO MANY ROWS 這個exception . 而顯式游標不執行第二次操作.

11-例：

1.(低效)SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO ;

2.高效:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT *

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

•13.用索引提高效率

通常,通過索引查詢數據比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引.

除了那些LONG或LONG RAW數據類型, 你可以索引幾乎所有的列. 在大型表中使用索引特別有效.

雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來 存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.

定期的重構索引是有必要的.

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>  

感謝各位的閱讀，以上就是“oracle SQL優化規則有哪些”的內容了，經過本文的學習后，相信大家對oracle SQL優化規則有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！