复合索引与其前导列

在建复合索引时，想到了自己建立的索引是否会真正用上，到网上查了一下，看到这个文章，转了过来。

感谢原博主，原文链接：http://blog.csdn.net/nickbest85/article/details/5681359

 

在oracle中，我们经常以为建立了索引，sql查询的时候就会如我们所希望的那样使用索引，事实上，oracle只会在一定条件下使用索引，这里我们总结数第一点：oracle会在条件中包含了前导列时使用索引，即查询条件中必须使用索引中的第一个列，请看下面的例子
  
 
SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

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

BONUS                          TABLE

DEPT                           TABLE

DUMMY                          TABLE

EMP                            TABLE

SALGRADE                       TABLE

 

建立一个联合索引（注意复合索引的索引列顺序）

SQL> create index emp_id1 on emp(empno,ename,deptno);
 
 
下面的查询由于没有使用到复合索引的前导列，所以没有使用索引

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

--------------------------------------------------------------------
 
 
下面的查询也由于没有使用到复合索引的前导列，所以没有使用索引

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

--------------------------------------------------------------------
 
下面的查询使用了复合索引中的前导列，所以查询走索引了

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

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

 

 

最后加上如何查看执行计划，也是从网上看到的，摘抄下如：

第一步：让oracle解释自己的SQL语句：

SQL>EXPLAIN PLAN FOR
SELECT * FROM mytable where cxsj>'20121102'; --要解析的SQL脚本

第二步：查看解释后的结果
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

如果在执行第二步时提示PLAN_TABLE表不存在，则要执行以下SQL语句去创建这个表：

SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql

oracle 中还有个auto trace，研究一下，也可以查看执行计划。