Oracle有效建立索引的小技巧

数据库版本： SQLgt; select * from v$version; BANNER ---------------------------------------------------------------- Or

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