Why Does oracle not use indexes?

Why Does oracle not use indexes?

Today is. Let's summarize the reasons why oracle does not use indexes. Take notes while learning.

First, the number of rows varies.

There is a num_rows field in the view user_tables. This field records the number of lines of the corresponding object after statistics are collected. There is a num_distinct field in the user_tab_columns view, this field records the number of different values in each field. Oracle believes that when num_distinct is closer to num_rows, the better the index selection, the easier it is to use the index during query execution.

Type 2: Clustering factor:

What is a clustering factor?

Clustering factor is a value that measures the similarity between the data sequence of the index column and the data sequence of the table fields. We all know that the created tables are generally heap tables, that is, the data stored in the tables is not continued. to access data more quickly, we usually use indexes for data access, at this time, no index has a clustering factor. The closer the clustering factor is to the number of objects, the better the selectivity. The closer the clustering factor is to the number of rows in the table, the worse the selectivity.

I heard a friend once mentioned this question: "Why is it so slow for me to query a data in the test environment and query data in the production environment? The table structure is the same, and the data is the same .". Let's take a look at the number of clustering factors.

View clustering factor is represented by CLUSTERING_FACTOR in the user_ind_statistics view. Take a look at the official introduction:

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.

Generally, the size of clustering factors is similar to the I/o obtained from data. If the clustering factor is large, the relative physical or logical (generally) I/o overhead is very large, that is, the block is frequently read and repeatedly, and the consistent data acquisition is slow.

The long query view includes dba_ind_statistics and dba_tab_statistics.

Third: unequal conditions:

If you use unequal conditions when querying data, the oracle task will need to read most of the data blocks, and the index will be skipped. 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> 

We can see that when you use <>, the index will be skipped, but we can use hints (prompt) to force the database to use the index. Note that the not in or in keyword is similar <> the index will still be skipped, the alternative method is either to change the SQL query statement or to use the case when condition, you also need to create a function index.

Fourth: the statistical information is too old

In the cost-based optimizer, more data is estimated to be cast. When the data has been modified (such as a large number of dml operations), the statistics must be too old, therefore, when oracle chooses an execution plan based on more statistics, an incorrect execution plan may occur.

Oracle runs a task that automatically collects statistics at 10 Gb.

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ò?????8DD?￡

The content is canceled at 11 GB;

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> 

When the statistical information is too long, the dbms_stats package is usually used to collect the corresponding object information.

5. query with wildcard characters:

When the predicate condition contains the % or _ wildcard, oracle ignores the index, but the wildcard displacement can be used to solve the problem, as shown below:

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> 

Type 6: existence of a function in the predicate Condition

Oracle does not use indexes no matter whether implicit function conversion or function display exists in the predicate. For implicit function conversion, the example is as follows:

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

7. A null value exists in the predicate column.

If the column in the predicate condition has a null value, oracle skips the index because the null value does not exist in the index segment. However, if it is a composite index, one of the columns is null, if the other column is not null, the database still uses indexes.

Type 8: Skip the leading Column

This is the last reason that no index is used. If a composite index is created on the write-in column but the prefix column is not involved in the predicate condition, the index will be skipped.