Oracle有效建立索引的小技巧

6rlg96r65i

数据库版本：
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也有所降低。对于一个有多个字段的表，如果经常查询的只是其中两、三个字段，如用户表、客户表等，把常用字段一起建一个索引，可以起到不错的效果。