Why Does Oracle not use indexes?

Today, I will summarize the reasons why oracle does not use indexes. Take notes while learning. Clustering factor is a measure of the similarity between the data sequence of index columns and the data sequence of table fields.

Today, I will summarize the reasons why oracle does not use indexes. Take notes while learning. Clustering factor is a measure of the similarity between the data sequence of index columns and the data sequence of table fields.

Today, I will 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 DEPTNO
EMP_IDX1 EMP EMPNO

SQL> 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 30

14 rows selected.

SQL> set autotrace trace exp
SQL> select * from emp where empno <> 7900;

Execution Plan
----------------------------------------------------------
Plan hash value: 822536733