oracle SQL優(yōu)化規(guī)則有哪些

156次閱讀

共計(jì) 2742 個(gè)字符，預(yù)計(jì)需要花費(fèi) 7 分鐘才能閱讀完成。

這篇文章主要講解了“oracle SQL 優(yōu)化規(guī)則有哪些”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著丸趣 TV 小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“oracle SQL 優(yōu)化規(guī)則有哪些”吧！

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

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

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

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

2 WHERE 子句中的連接順序：

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

3. SELECT 子句中避免使用 lsquo;* lsquo;

ORACLE 在解析的過(guò)程中, 需要通過(guò)查詢數(shù)據(jù)字典將 * 依次轉(zhuǎn)換成所有的列名.

4. 使用表的別名(Alias)

當(dāng)在 SQL 語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè) Column 上. 這樣一來(lái), 就可以減少解析的時(shí)間并減少那些由 Column 歧義引起的語(yǔ)法錯(cuò)誤.

(Column 歧義指的是由于 SQL 中不同的表具有相同的 Column 名, 當(dāng) SQL 語(yǔ)句中出現(xiàn)這個(gè) Column 時(shí),SQL 解析器無(wú)法判斷這個(gè) Column 的歸屬)

5. 減少訪問(wèn)的次數(shù):

當(dāng)執(zhí)行每條 SQL 語(yǔ)句時(shí), ORACLE 在內(nèi)部執(zhí)行了許多工作: 解析 SQL 語(yǔ)句, 估算索引的利用率, 綁定變量, 讀數(shù)據(jù)塊等等. 由此可見(jiàn), 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù), 就能實(shí)際上減少 ORACLE 的工作量.

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

當(dāng)刪除表中的記錄時(shí), 在通常情況下, 回滾段(rollback segments) 用來(lái)存放可以被恢復(fù)的信息. 如果你沒(méi)有 COMMIT 事務(wù),ORACLE 會(huì)將數(shù)據(jù)恢復(fù)到執(zhí)行刪除命令之前的狀況.

而當(dāng)運(yùn)用 TRUNCATE 時(shí), 回滾段不再存放任何可被恢復(fù)的信息. 當(dāng)命令運(yùn)行后, 數(shù)據(jù)不能被恢復(fù). 因此很少的資源被調(diào)用, 執(zhí)行時(shí)間也會(huì)很短.

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

7. (可能的話)使用 COMMIT

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

COMMIT 所釋放的資源:

a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息.

b. 被程序語(yǔ)句獲得的鎖

c. redo log buffer 中的空間

d. ORACLE 為管理上述 3 種資源中的內(nèi)部花費(fèi)

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

盡量少使用 HAVING 子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾. 這個(gè)處理需要排序, 總計(jì)等操作. 如果能通過(guò) WHERE 子句限制記錄的數(shù)目, 那就能減少這方面的開(kāi)銷.

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)

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

原因分析：

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

執(zhí)行的過(guò)程相當(dāng)于:

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)

執(zhí)行的過(guò)程相當(dāng)于:

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 要被完全掃描一遍.

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

10. 用表連接替換 EXISTS

改進(jìn)第 9 打優(yōu)化規(guī)則的例子.

11. 用 EXISTS 替換 DISTINCT

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

立刻返回結(jié)果. DISTINCT 會(huì)先進(jìn)行排序, 然后會(huì)根據(jù)排序后的順序去除相同的行.

12. 使用顯式的游標(biāo)(CURSOR)

使用隱式的游標(biāo), 將會(huì)執(zhí)行兩次操作. 第一次檢索記錄, 第二次檢查 TOO MANY ROWS 這個(gè) exception . 而顯式游標(biāo)不執(zhí)行第二次操作.

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);

bull;13. 用索引提高效率

通常, 通過(guò)索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng) ORACLE 找出執(zhí)行查詢和 Update 語(yǔ)句的最佳路徑時(shí), ORACLE 優(yōu)化器將使用索引.

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

雖然使用索引能得到查詢效率的提高, 但是我們也必須注意到它的代價(jià). 索引需要空間來(lái) 存儲(chǔ), 也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的 INSERT , DELETE , UPDATE 將為此多付出 4 , 5 次的磁盤(pán) I /O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理, 那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.

定期的重構(gòu)索引是有必要的.

ALTER INDEX INDEXNAME REBUILD TABLESPACENAME

感謝各位的閱讀，以上就是“oracle SQL 優(yōu)化規(guī)則有哪些”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì) oracle SQL 優(yōu)化規(guī)則有哪些這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

正文完