Oracle索引的使用規則
首先,我們要確定資料庫執行在何種優化模式下,相應的引數是:optimizer_mode。可在svrmgrl中執行“show parameter optimizer_mode"來檢視。ORACLE V7以來預設的設定應是"choose",即如果對已分析的表查詢的話選擇CBO,否則選擇RBO。如果該引數設為“rule”,則不論表是否分析過,一概選用RBO,除非在語句中用hint強制。
其次,檢查被索引的列或組合索引的首列是否出現在PL/SQL語句的WHERE子句中,這是“執行計劃”能用到相關索引的必要條件。
第三,看採用了哪種型別的連線方式。ORACLE的共有Sort Merge Join(SMJ)、Hash Join(HJ)和Nested Loop Join(NL)。在兩張表連線,且內表的目標列上建有索引時,只有Nested Loop才能有效地利用到該索引。SMJ即使相關列上建有索引,最多隻能因索引的存在,避免資料排序過程。HJ由於須做HASH運算,索引的存在對資料查詢速度幾乎沒有影響。
第四,看連線順序是否允許使用相關索引。假設表emp的deptno列上有索引,表dept的列deptno上無索引,WHERE語句有emp.deptno=dept.deptno條件。在做NL連線時,emp做為外表,先被訪問,由於連線機制原因,外表的資料訪問方式是全表掃描,emp.deptno上的索引顯然是用不上,最多在其上做索引全掃描或索引快速全掃描。
第五,是否用到系統資料字典表或檢視。由於系統資料字典表都未被分析過,可能導致極差的“執行計劃”。但是不要擅自對資料字典表做分析,否則可能導致死鎖,或系統效能下降。
第六,索引列是否函式的引數。如是,索引在查詢時用不上。
第七,是否存在潛在的資料型別轉換。如將字元型資料與數值型資料比較,ORACLE會自動將字元型用to_number()函式進行轉換,從而導致第六種現象的發生。
第八,是否為表和相關的索引蒐集足夠的統計資料。對資料經常有增、刪、改的表最好定期對錶和索引進行分析,可用SQL語句“analyze table xxxx compute statistics for all indexes;"。ORACLE掌握了充分反映實際的統計資料,才有可能做出正確的選擇。
第九,索引列的選擇性不高。
我們假設典型情況,有表emp,共有一百萬行資料,但其中的emp.deptno列,資料只有4種不同的值,如10、20、30、40。雖然emp資料行有很多,ORACLE預設認定表中列的值是在所有資料行均勻分佈的,也就是說每種deptno值各有25萬資料行與之對應。假設SQL搜尋條件DEPTNO=10,利用deptno列上的索引進行資料搜尋效率,往往不比全表掃描的高,ORACLE理所當然對索引“視而不見”,認為該索引的選擇性不高。
但我們考慮另一種情況,如果一百萬資料行實際不是在4種deptno值間平均分配,其中有99萬行對應著值10,5000行對應值20,3000行對應值30,2000行對應值40。在這種資料分佈圖案中對除值為10外的其它deptno值搜尋時,毫無疑問,如果索引能被應用,那麼效率會高出很多。我們可以採用對該索引列進行單獨分析,或用analyze語句對該列建立直方圖,對該列蒐集足夠的統計資料,使ORACLE在搜尋選擇性較高的值能用上索引。
第十,索引列值是否可為空(NULL)。如果索引列值可以是空值,在SQL語句中那些需要返回NULL值的操作,將不會用到索引,如COUNT(*),而是用全表掃描。這是因為索引中儲存值不能為全空。
第十一,看是否有用到並行查詢(PQO)。並行查詢將不會用到索引。
第十二,看PL/SQL語句中是否有用到bind變數。由於資料庫不知道bind變數具體是什麼值,在做非相等連線時,如“<”,“>”,“like”等。ORACLE將引用預設值,在某些情況下會對執行計劃造成影響。
其次,檢查被索引的列或組合索引的首列是否出現在PL/SQL語句的WHERE子句中,這是“執行計劃”能用到相關索引的必要條件。
第三,看採用了哪種型別的連線方式。ORACLE的共有Sort Merge Join(SMJ)、Hash Join(HJ)和Nested Loop Join(NL)。在兩張表連線,且內表的目標列上建有索引時,只有Nested Loop才能有效地利用到該索引。SMJ即使相關列上建有索引,最多隻能因索引的存在,避免資料排序過程。HJ由於須做HASH運算,索引的存在對資料查詢速度幾乎沒有影響。
第四,看連線順序是否允許使用相關索引。假設表emp的deptno列上有索引,表dept的列deptno上無索引,WHERE語句有emp.deptno=dept.deptno條件。在做NL連線時,emp做為外表,先被訪問,由於連線機制原因,外表的資料訪問方式是全表掃描,emp.deptno上的索引顯然是用不上,最多在其上做索引全掃描或索引快速全掃描。
第五,是否用到系統資料字典表或檢視。由於系統資料字典表都未被分析過,可能導致極差的“執行計劃”。但是不要擅自對資料字典表做分析,否則可能導致死鎖,或系統效能下降。
第六,索引列是否函式的引數。如是,索引在查詢時用不上。
第七,是否存在潛在的資料型別轉換。如將字元型資料與數值型資料比較,ORACLE會自動將字元型用to_number()函式進行轉換,從而導致第六種現象的發生。
第八,是否為表和相關的索引蒐集足夠的統計資料。對資料經常有增、刪、改的表最好定期對錶和索引進行分析,可用SQL語句“analyze table xxxx compute statistics for all indexes;"。ORACLE掌握了充分反映實際的統計資料,才有可能做出正確的選擇。
第九,索引列的選擇性不高。
我們假設典型情況,有表emp,共有一百萬行資料,但其中的emp.deptno列,資料只有4種不同的值,如10、20、30、40。雖然emp資料行有很多,ORACLE預設認定表中列的值是在所有資料行均勻分佈的,也就是說每種deptno值各有25萬資料行與之對應。假設SQL搜尋條件DEPTNO=10,利用deptno列上的索引進行資料搜尋效率,往往不比全表掃描的高,ORACLE理所當然對索引“視而不見”,認為該索引的選擇性不高。
但我們考慮另一種情況,如果一百萬資料行實際不是在4種deptno值間平均分配,其中有99萬行對應著值10,5000行對應值20,3000行對應值30,2000行對應值40。在這種資料分佈圖案中對除值為10外的其它deptno值搜尋時,毫無疑問,如果索引能被應用,那麼效率會高出很多。我們可以採用對該索引列進行單獨分析,或用analyze語句對該列建立直方圖,對該列蒐集足夠的統計資料,使ORACLE在搜尋選擇性較高的值能用上索引。
第十,索引列值是否可為空(NULL)。如果索引列值可以是空值,在SQL語句中那些需要返回NULL值的操作,將不會用到索引,如COUNT(*),而是用全表掃描。這是因為索引中儲存值不能為全空。
第十一,看是否有用到並行查詢(PQO)。並行查詢將不會用到索引。
第十二,看PL/SQL語句中是否有用到bind變數。由於資料庫不知道bind變數具體是什麼值,在做非相等連線時,如“<”,“>”,“like”等。ORACLE將引用預設值,在某些情況下會對執行計劃造成影響。
相關文章
- ORACLE 索引使用規Oracle索引
- 索引使用的基本原則索引
- MySQL索引選擇及規則整理MySql索引
- 六點SQL Server索引管理規則SQLServer索引
- oracle10g的正則規則匹配Oracle
- oracle undo分配規則Oracle
- Oracle索引HINT的使用Oracle索引
- Oracle索引規劃設計Oracle索引
- 對線面試官:Mysql組合索引的生效規則面試MySql索引
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- Oracle OCP(18):命名規則Oracle
- ORACLE 監控索引的使用Oracle索引
- Oracle DBA速成法的潛規則Oracle
- MySQL觸發器的使用規則MySql觸發器
- MySQL索引(二):建索引的原則MySql索引
- 使用DBV的命令規則和規則集強化資料庫安全資料庫
- 聯合索引和多個單列索引使用中的索引命中情況及索引建立原則索引
- Oracle資料庫索引管理規範Oracle資料庫索引
- oracle 索引使用及索引失效總結Oracle索引
- jQuery Validate驗證規則的使用jQuery
- 使用IPtables搭建防火牆的規則(轉)防火牆
- MongoDB正規表示式在索引中的使用MongoDB索引
- 索引的選擇原則索引
- iptables 常用規則使用例項
- Iptables防火牆規則使用梳理防火牆
- oracle order by索引是否使用的情況Oracle索引
- Oracle 索引的使用情況檢視Oracle索引
- 如何監控oracle的索引是否使用Oracle索引
- Oracle 監控索引的使用率Oracle索引
- 監控Oracle索引的使用情況Oracle索引
- Oracle 10g模式物件名稱的規則Oracle 10g模式物件
- 應遵循的PL/SQL編碼規則 - ORACLE (zt)SQLOracle
- drools規則屬性(rule attributes)的使用
- [Unity2D]指令碼的使用規則Unity指令碼
- Oracle索引限制、實用準則及其建議<轉>Oracle索引
- 關於laravel使用自定義驗證規則後某些規則不生效Laravel
- oracle資料隱式轉換規則Oracle
- oracle不同版本之間exp/imp規則Oracle