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

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

Http://blog.csdn.net/lunar2000/archive/2004/11/24/193470.aspx

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 summarize the 1th:Oracle The index is used when the condition contains a leading column, that is, the first column in the index must be used in the query condition , see the example below

Sql> select * from tab;<?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>

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

</