oracle 不使用索引的原因有哪些？

oracle 不使用索引的原因有哪些？

   今天是2014-02-07，今天開始總結一下oracle不使用索引的原因有哪些。一邊學習一邊做筆記。

第一種：行數存在差異。

   在視圖user_tables存在一個num_rows欄位，該欄位是記錄在統計資訊收集後所對應對象的行數，在user_tab_columns視圖中存在一個num_distinct欄位，該欄位記錄每個欄位內不同數值的個數。oracle認為當num_distinct越接近num_rows的時候索引的選擇性越好，那麼在執行查詢的時候越容易使用索引。

第二種：聚簇因子：

什麼是聚簇因子？

 聚簇因子是衡量索引列資料順序與表欄位資料順序相似性的一個值。我們都知道在建立的表中一般都是堆表，也就是資料在表中儲存是無續的，那麼為了更加快速的訪問資料，我們通常使用索引進行資料訪問，這時候沒個索引都有一個聚簇因子，聚簇因子越接近對象的塊數，那麼選擇性越好，越接近表的行數那麼選擇性越差。

之前聽到有個朋友曾經提到這麼一個問題“為什麼我在測試環境查詢一個資料很快和在生產環境查詢資料怎麼這麼慢呢？表結構都一樣的，資料也是一樣的。”。那麼不妨看看聚簇因子是多少。

聚簇因子的查看是從user_ind_statistics視圖中： CLUSTERING_FACTOR    表示的。看一下官方介紹：

Indicates the amount of order of the rows in the table based on the values of the index.

• If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.

• If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.

往往聚簇因子的大小和資料擷取的I/o存在一定的相似性。如果聚簇因子大，那麼相對的物理或是邏輯（一般是）i/o開銷很大，也就是塊被頻繁反覆讀取，一致資料擷取很慢。

長查詢的視圖有dba_ind_statistics和dba_tab_statistics

第三種：使用不等條件：

 當使用在進行查詢資料的時候使用不等條件<>，那麼oracle任務這個符號會需要讀取大部分的資料區塊，那麼就會跳過使用索引。eg：

SQL> select index_name,table_name,column_name from user_ind_columns where table_name='EMP';INDEX_NAME                     TABLE_NAME                     COLUMN_NAME------------------------------ ------------------------------ ----------------------------------------EMP_IDX1                       EMP                            DEPTNOEMP_IDX1                       EMP                            EMPNOSQL> select * from emp;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7900 JAMES      CLERK           7698 03-DEC-81        950                    3014 rows selected.SQL> set autotrace trace expSQL> select * from emp where empno<>7900;Execution Plan----------------------------------------------------------Plan hash value: 822536733--------------------------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |    13 |   494 |    14   (0)| 00:00:01 |       |       ||   1 |  PARTITION RANGE ALL|      |    13 |   494 |    14   (0)| 00:00:01 |     1 |     4 ||*  2 |   TABLE ACCESS FULL | EMP  |    13 |   494 |    14   (0)| 00:00:01 |     1 |     4 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("EMPNO"<>7900)SQL> select /*+index(emp EMP_IDX1)*/ * from emp where empno<>7900;Execution Plan----------------------------------------------------------Plan hash value: 257372123---------------------------------------------------------------------------------------------------------------| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                   |          |    13 |   494 |     6   (0)| 00:00:01 |       |       ||   1 |  PARTITION RANGE ALL               |          |    13 |   494 |     6   (0)| 00:00:01 |     1 |     4 ||   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| EMP      |    13 |   494 |     6   (0)| 00:00:01 |     1 |     4 ||*  3 |    INDEX FULL SCAN                 | EMP_IDX1 |    13 |       |     3   (0)| 00:00:01 |     1 |     4 |---------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("EMPNO"<>7900)SQL> 

可知，當使用<>就會跳過索引，但是我們可以使用hints（提示）讓資料庫強制使用索引，注意not in或是in 關鍵字類似<>依然會跳過索引，那麼替代辦法要麼更改sql查詢語句，要麼使用case when條件，那麼也需要在建立函數索引了。

第四種：統計資訊過舊

 在基於成本的optimizer更具資料進行估計cast，當資料已經被修改（如進行了大量的dml操作），那麼統計資訊肯定過舊，那麼oracle在更加統計資訊進行執行計畫選擇的時候往往可能出現選擇錯誤的執行計畫。

 在10g開始oracle會有自動收集統計資訊的任務在運行。

17:15:22 sys@REPDB>select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE    10.2.0.5.0      ProductionTNS for HPUX: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production17:15:28 sys@REPDB>select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_jobs;JOB_NAME                       LAST_START_DATE                                                             COMMENTS------------------------------ --------------------------------------------------------------------------- ------------------------------------------------------------PURGE_LOG                      07-2Ô -14 03.00.01.146182 ÉÏÎç +08:00                                      purge log jobFGR$AUTOPURGE_JOB                                                                                          file group auto-purge jobGATHER_STATS_JOB                                                                                           Oracle defined automatic optimizer statistics collection jobAUTO_SPACE_ADVISOR_JOB                                                                                     auto space advisor maintenance jobMGMT_CONFIG_JOB                06-2Ô -14 10.00.02.198895 ÏÂÎç +08:00                                      Configuration collection job.MGMT_STATS_CONFIG_JOB          01-2Ô -14 01.01.01.762793 ÉÏÎç +08:00                                      OCM Statistics collection job.RLM$EVTCLEANUP                 07-2Ô -14 04.18.33.923785 ÏÂÎç +08:00RLM$SCHDNEGACTION              07-2Ô -14 05.02.43.177946 ÏÂÎç +08:00ÒÑÑ¡Ôñ8ÐС£

但在11g該內容開始取消；

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0      ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - ProductionSQL> select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_jobs;JOB_NAME                       LAST_START_DATE                                    COMMENTS------------------------------ -------------------------------------------------- --------------------------------------------------XMLDB_NFS_CLEANUP_JOBSM$CLEAN_AUTO_SPLIT_MERGE      07-FEB-14 12.00.00.522780 AM PST8PDT               auto clean job for auto split mergeRSE$CLEAN_RECOVERABLE_SCRIPT   07-FEB-14 12.00.00.242715 AM PST8PDT               auto clean job for recoverable scriptFGR$AUTOPURGE_JOB                                                                 file group auto-purge jobBSLN_MAINTAIN_STATS_JOB        06-FEB-14 10.39.20.219977 PM -07:00                Oracle defined automatic moving window baseline st                                                                                  atistics computation jobDRA_REEVALUATE_OPEN_FAILURES   31-JAN-14 06.00.02.807655 AM PST8PDT               Reevaluate open failures for DRAHM_CREATE_OFFLINE_DICTIONARY                                                      Create offline dictionary in ADR for DRA name tran                                                                                  slationORA$AUTOTASK_CLEAN             06-FEB-14 09.39.19.975070 PM PST8PDT               Delete obsolete AUTOTASK repository dataFILE_WATCHER                                                                      File watcher jobPURGE_LOG                      06-FEB-14 09.39.19.999313 PM PST8PDT               purge log jobMGMT_STATS_CONFIG_JOB          06-FEB-14 10.39.19.607560 PM -07:00                OCM Statistics collection job.MGMT_CONFIG_JOB                07-FEB-14 01.01.01.661585 AM -07:00                Configuration collection job.RLM$SCHDNEGACTION              07-FEB-14 04.32.12.069752 PM +08:00RLM$EVTCLEANUP                 07-FEB-14 01.49.46.407904 AM -07:0014 rows selected.SQL> 

那麼當統計資訊過久的時候通常使用dbms_stats包進行相應對象資訊的收集。

第五種：使用萬用字元查詢：

當謂詞條件中存在%或是_萬用字元的時候，oracle會忽略索引，但是可以通過萬用字元位移解決，如下：

SQL> create index emp_idx2 on emp(ename);Index created.SQL> set autotrace trace expSQL> select * from emp where ename like '%OT%';Execution Plan----------------------------------------------------------Plan hash value: 822536733--------------------------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |     1 |    38 |    14   (0)| 00:00:01 |       |       ||   1 |  PARTITION RANGE ALL|      |     1 |    38 |    14   (0)| 00:00:01 |     1 |     4 ||*  2 |   TABLE ACCESS FULL | EMP  |     1 |    38 |    14   (0)| 00:00:01 |     1 |     4 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("ENAME" LIKE '%OT%')SQL> SELECT * FROM EMP WHERE ENAME LIKE 'SC%OT%';Execution Plan----------------------------------------------------------Plan hash value: 547783664---------------------------------------------------------------------------------------------------------------| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                   |          |     1 |    38 |     2   (0)| 00:00:01 |       |       ||   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| EMP      |     1 |    38 |     2   (0)| 00:00:01 | ROWID | ROWID ||*  2 |   INDEX RANGE SCAN                 | EMP_IDX2 |     1 |       |     1   (0)| 00:00:01 |       |       |---------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ENAME" LIKE 'SC%OT%')       filter("ENAME" LIKE 'SC%OT%')SQL> 

第六種：在謂詞條件中存在函數

無論在謂詞中存在隱式函數轉換或是顯示函數那麼oracle都不會使用索引，對於隱式函數轉換例子如下：

http://blog.csdn.net/rhys_oracle/article/details/18011077

第七種：謂詞列存在null值。

當在謂詞條件中的列存在null值，那麼oracle將跳過索引，因為null值不存在索引段中，但是如果是複合索引，其中一列為null，另外一列不為null，那麼資料庫依然使用索引。

第八種：跳過前置列

 這是最後一種不使用索引的原因，那就是如果是在默寫列上建立了複合索引，但在謂詞條件中沒有涉及到前置列，那麼將會是跳過索引。，

 

如上是oracle不使用索引情況的一點學習筆記。如果強制使用索引，那麼可以嘗試相關hints如下：

http://blog.csdn.net/rhys_oracle/article/details/18734071