以下文章轉載來自 oracle園地


另外聽說

IN 適合內外表都很大的情況,EXISTS 適合外表結果集很小的情況。


基本的Sql編寫注意事項

  • 儘量少用 IN 操作符,基本上所有的IN操作符都可以用EXISTS代替。
  • 不用NOT IN操作符,可以用NOT EXISTS或者外連接+替代。
  • Oracle 在執行IN子查詢時,首先執行子查詢,將查詢結果放入臨時表再執行主查詢。而EXISTS則是首先檢查主查詢,然後運行子查詢直到找到第一個匹配項。( Robert : 所以有EXISTS 快於 IN 之說 )
  • NOT EXISTS比NOT IN效率稍高。但具體在選擇IN或EXIST操作時,要根據主子表資料量大小來具體考慮。
  • 不用「<>」或者「!=」操作符。對不等於操作符的處理會造成全表掃瞄,可以用「<」 or 「>」代替。
  • Where子句中出現IS NULL或者IS NOT NULL時,Oracle會停止使用索引而執行全表掃瞄。可以考慮在設計表時,對索引列設置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。
  • 當通配符「%」或者「_」作為查詢字符串的第一個字符時,索引不會被使用。
  • 對於有連接的列「||」,最後一個連接列索引會無效。儘量避免連接,可以分開連接或者使用不作用在列上的函數替代。
  • 如果索引不是基於函數的,那麼當在Where子句中對索引列使用函數時,索引不再起作用。
  • Where 子句中避免在索引列上使用計算,否則將導致索引失效而進行全表掃瞄
  • 對資料類型不同的列進行比較時,會使索引失效。
  • 用「>=」替代「>」
  • UNION 操作符會對結果進行篩選,消除重複,資料量大的情況下可能會引起磁盤排序。如果不需要刪除重複記錄,應該使用UNION ALL。
  • Oracle 從下到上處理 Where 子句中多個查詢條件,所以表連接語句應寫在其他Where條件前,可以過濾掉最大數量記錄的條件必須寫在Where子句的末尾。
  • Oracle從右到左處理From子句中的表名,所以在From子句中包含多個表的情況下,將記錄最少的表放在最後。(只在採用RBO優化時有效,下文詳述)
  • Order By語句中的非索引列會降低性能,可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式。
  • 不同區域出現的相同的Sql語句,要保證查詢字符完全相同,以利用SGA共享池,防止相同的Sql語句被多次分析。
  • 多利用內部函數提高Sql效率。
  • 當在Sql語句中連接多個表時,使用表的別名,並將之作為每列的前置碼。這樣可以減少解析時間。

需要注意的是,隨著Oracle的昇級,查詢優化器會自動對Sql語句進行優化,某些限制可能在新版本的Oracle下不再是問題。尤其是採用CBO(Cost-Based Optimization,基於代價的優化方式)時。

我們可以總結一下可能引起全表掃瞄的操作:

  • 在索引列上使用NOT或者「<>」;
  • 對索引列使用函數或者計算;
  • NOT IN操作;
  • 通配符 % 位於查詢字符串的第一個字符;
  • IS NULL或者IS NOT NULL;
  • 多列索引,但它的第一個列並沒有被Where子句引用;


Oracle優化器

Oracle優化器(Optimizer)是Oracle在執行SQL之前分析語句的工具。
Oracle的優化器有兩種優化方式:基於規則的(RBO)和基於代價的(CBO)。

  • RBO:優化器遵循Oracle內部預定的規則。
  • CBO:依據語句執行的代價,主要指對CPU和記憶體的佔用。優化器在判斷是否使用CBO時,要參照表和索引的統計資訊。統計資訊要在對表做analyze後才會有。Oracle8及以後版本,推薦用CBO方式。

Oracle優化器的優化模式主要有四種:

  • Rule:基於規則;
  • Choose:預設模式。根據表或索引的統計資訊,如果有統計資訊,則使用CBO方式;如果沒有統計資訊,相應列有索引,則使用RBO方式。
  • First rows:與Choose類似。不同的是如果表有統計資訊,它將以最快的方式返回查詢的前幾行,以獲得最佳響應時間。
  • All rows:即完全基於Cost的模式。當一個表有統計資訊時,以最快方式返回表所有行,以獲得最大吞吐量。沒有統計資訊則使用RBO方式。

設定優化模式的方式

  • Instance級別:在init<SID>.ora文件中設定OPTIMIZER_MODE;
  • Session級別:通過SQL> ALTER SESSION SET OPTIMIZER_MODE=;來設定。
  • 語句級別:通過SQL> SELECT /*+ALL+_ROWS*/ ……;來設定。可用的HINT包括/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/ 等。

 

要注意的是,如果表有統計資訊,則可能造成語句不走索引的結果。

可以用SQL>ANALYZE TABLE table_name DELETE STATISTICS; 刪除索引。

對列和索引更新統計資訊的SQL:
SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;
SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;


羅伯特 發表在 痞客邦 PIXNET 留言(0) 人氣()