One of the pitfalls of using indexes: leading columns that do not use a composite index cause the query to not use the index

One of the pitfalls of indexing using indexes: Leading columns that do not use a composite index cause the query to not use the index
In Oracle, we often assume that indexed, SQL queries use the index as we would like, in fact, Oracle only uses indexes under certain conditions, and here we sum up the 1th: Oracle uses indexes when they include leading columns in the condition. This means that the first column in the index must be used in the query condition, as shown in the following example

Sql> select * from tab;



Tname Tabtype Clusterid

------------------------------ ------- ----------

BONUS TABLE

DEPT TABLE

DUMMY TABLE

EMP TABLE

Salgrade TABLE



Establish a federated index (note indexed column order for composite indexes)

Sql> CREATE INDEX EMP_ID1 on EMP (EMPNO,ENAME,DEPTNO);



Index created



Establish a single key index

Sql> CREATE INDEX Emp_id2 on EMP (SAL);



Index created





Sql> Select Table_name,index_name from User_indexes

2 where table_name= ' EMP ';



TABLE_NAME Index_name

------------------------------ ------------------------------

EMP EMP_ID1

EMP Emp_id2



Sql> SELECT * from User_ind_columns

2/



INDEX_NAME TABLE_NAME column_name Column_position column_length Char_length Descend

------------------------------ ------------------------------ --------------------------------------------------- ----------------------------- --------------- ------------- ----------- -------

emp_id1                         emp                             empno                                                                                               1             22            0 ASC

emp_id1                         emp                             ename                                                                                               2             10          Ten ASC

emp_id1                         emp                             DEPTNO                                                                                              3             22           0 ASC

emp_id2                         emp                             sal                                                                                                 1             22            0 ASC



The following query does not use an index because it does not use a leading column for a composite index

Select Job, empno from emp where ename= ' RICH ';



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | TABLE ACCESS Full |       EMP |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("EMP".) ename "= ' RICH ')

Note:rule Based Optimization



Rows selected





The following query also does not use an index because it does not use a leading column for a composite index

Select Job, empno from EMP where deptno=30;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | TABLE ACCESS Full |       EMP |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("EMP".) DEPTNO "=30)

Note:rule Based Optimization



Rows selected







The following query uses the leading columns in the composite index, so the query walks the index

Select Job, empno from EMP where empno=7777;



Plan_table_output

--------------------------------------------------------------------------------

---------------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

---------------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|  1 | TABLE ACCESS by INDEX rowid|       EMP |       |       | |

|* 2 | INDEX RANGE SCAN |       Emp_id1 |       |       | |

---------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("EMP".) EMPNO "=7777)

Note:rule Based Optimization



Rows selected









The following query uses the first column and the second column in the composite index, so the query walks the index

Select Job, empno from EMP where empno=7777 and ename= ' RICH ';



Plan_table_output

--------------------------------------------------------------------------------

---------------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

---------------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|  1 | TABLE ACCESS by INDEX rowid|       EMP |       |       | |

|* 2 | INDEX RANGE SCAN |       Emp_id1 |       |       | |

---------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("EMP".) EMPNO "=7777 and" EMP. " ename "= ' RICH ')

Note:rule Based Optimization



Rows selected







All columns of a composite index are used, so the index is indexed, and because the selected column (Job) is not included in the index,

So after the index full table scan gets the ROWID that satisfies the condition, also must go to the table to retrieve the corresponding row

Select Job, empno from EMP where empno=7777 and Ename= ' RICH ' and deptno=30;



Plan_table_output

--------------------------------------------------------------------------------

---------------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

---------------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|  1 | TABLE ACCESS by INDEX rowid|       EMP |       |       | |

|* 2 | INDEX RANGE SCAN |       Emp_id1 |       |       | |

---------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("EMP".) EMPNO "=7777 and" EMP. " ename "= ' RICH ' and ' EMP '." Dep

TNO "=30)

Note:rule Based Optimization



Rows selected









All the columns of the composite index are used, so the index is gone, and because all the selected columns are included in the index, only the index range scan

Select Empno from emp where empno=7777 and Ename= ' RICH ' and deptno=30;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | INDEX RANGE SCAN |       Emp_id1 |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-access ("EMP".) EMPNO "=7777 and" EMP. " ename "= ' RICH ' and" EM

P "." DEPTNO "=30)

Note:rule Based Optimization



Rows selected