Oracle有效建立索引的小技巧

数据库版本：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

以SCOTT用户的表为例，看一下表DEPT的索引：
SQL> select index_name,column_name,column_position from user_ind_columns where table_name="DEPT";

INDEX_NAME           COLUMN_NAME                              COLUMN_POSITION
-------------------- ---------------------------------------- ---------------
PK_DEPT              DEPTNO                                                 1

分析一下表：
SQL> analyze table dept estimate statistics;
SQL> analyze table emp estimate statistics;

执行两个查询：
SQL> select deptno,dname from dept;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE （Cost=2 Card=4 Bytes=44）
   1    0   TABLE ACCESS （FULL） OF "DEPT" （Cost=2 Card=4 Bytes=44）

SQL> select e.ename,d.dname from emp e,dept d where d.deptno=e.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE （Cost=5 Card=14 Bytes=252）
   1    0   HASH JOIN （Cost=5 Card=14 Bytes=252）
   2    1     TABLE ACCESS （FULL） OF "DEPT" （Cost=2 Card=4 Bytes=44）
   3    1     TABLE ACCESS （FULL） OF "EMP" （Cost=2 Card=14 Bytes=98）

可以看到，两个查询都是全表扫描。如果DEPT表比较小，全表扫描也不错，但数据量大似乎不太好。

建个联合索引，
SQL> create index idx_dept_multi on dept （deptno,dname）;

重新分析一下表DEPT，
SQL> analyze table dept estimate statistics;

再执行上面的两个查询。
SQL> select deptno,dname from dept;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE （Cost=1 Card=4 Bytes=44）
   1    0   INDEX （FULL SCAN） OF "IDX_DEPT_MULTI" （NON-UNIQUE）（Cost=1 Card=4 Bytes=44）

SQL> select e.ename,d.dname from emp e,dept d where d.deptno=e.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE （Cost=4 Card=14 Bytes=252）
   1    0   HASH JOIN （Cost=4 Card=14 Bytes=252）
   2    1    INDEX （FULL SCAN） OF "IDX_DEPT_MULTI" （NON-UNIQUE）（Cost =1 Card=4 Bytes=44）
   3    1     TABLE ACCESS （FULL） OF "EMP" （Cost=2 Card=14 Bytes=98）

通过执行计划，看到DEPT已经不是全表扫描了，COST也有所降低。对于一个有多个字段的表，如果经常查询的只是其中两、三个字段，如用户表、客户表等，把常用字段一起建一个索引，可以起到不错的效果。 Oracle手工创建数据库的步骤Oracle主备库配置过程相关资讯 Oracle基础教程

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）
Oracle 管理之 Linux 网络基础（02/16/2013 18:37:35）

本文评论查看全部评论（0）