复合索引8i9i10g使用效果的对比

这个是8i的版本的数据库

SQL> select *from V$version;

BANNER

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

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

PL/SQL Release 8.1.7.4.0 - Production

CORE   8.1.7.0.0      Production

TNS for HPUX: Version 8.1.7.4.0 - Production

NLSRTL Version 3.4.1.0.0 - Production

然后我创建一个测试table EMPL !

SQL> create table empl(A number,B number,C number,D varchar2(20));

Table created.

再创建索引

SQL> create index em_dx on empl(A,B,C);

Index created.

Insert into empl values(4,4,4,’sam’);

插入数据这一步我就省了,里面有20笔数据

SQL> select count(*) from kyo.empl;

 COUNT(*)

----------

       20

然后我们开始查询,可以看出当where条件匹配3个的时候走的是index

select *from empl where A=1 and B=2 and c=3;

        A         B         C D

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

        1         2         3 xiaochun

create table empl as select owner a,object_id b,rownum c ,object_name d from dba_objects;

Execution Plan

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

  0     SELECT STATEMENT ptimizer=CHOOSE

  1   0  TABLE ACCESS (BY INDEX ROWID) OF 'EMPL'

  2   1    INDEX (RANGE SCAN) OF 'EM_DX' (NON-UNIQUE)

然后我们指定where复合索引的主索引键a ,从下面的执行计划可以看出任务是index扫描.

select *from empl where a=1;

        A         B         C D

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

        1         1         1 zhangsan

        1         1         1 zhangsan

        1         1         1 zhangsan

        1         1         1 zhangsan

        1         1         1 zhangsan

        1         1         1 zhangsan

        1         1         1 zhangsan

        1         1         1 zhangsan

        1         2         3 xiaochun

9 rows selected.

Execution Plan

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

  0     SELECT STATEMENT ptimizer=CHOOSE

  1   0  TABLE ACCESS (BY INDEX ROWID) OF 'EMPL'

  2   1    INDEX (RANGE SCAN) OF 'EM_DX' (NON-UNIQUE)

然后我指定where条件为复合索引的非主索引键B,可以看出它走的是全表扫描.

SQL> select *from empl where b=2;

        A         B         C D

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

        2         2         2 lise

        2         2         2 lise

        2         2         2 lise

        2         2         2 lise

        1         2         3 xiaochun

Execution Plan

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

  0     SELECT STATEMENT ptimizer=CHOOSE

  1   0  TABLE ACCESS (FULL)OF 'EMPL'

我们指定where条件为另外两个非主键b ,c从执行计划可以看出依然是全表扫描.

SQL> select *from empl where b=2 and c=2 ;

        A         B         C D

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

        2         2         2 lise

        2         2         2 lise

        2         2         2 lise

        2         2         2 lise

Execution Plan

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

  0     SELECT STATEMENT ptimizer=CHOOSE

  1   0  TABLE ACCESS (FULL)OF 'EMPL'

所以,我们可以得出在8i数据库中如果建立的复合索引,where条件中必须指定主索引键,在执行计划中才会使用索引,否则就使用全表扫描.

下面的是9i的测试

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 Linux: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 – Production

这个是9i的数据库

然后我创建一个测试table EMPL !

SQL> create table empl(A number,B number,C number,D varchar2(20));

Table created.

再创建索引

SQL> create index em_dx on empl(A,B,C);

Index created.

然后插入数据查看下table的记录数

SQL> select count(*) from empl;

 COUNT(*)

----------

        9

看下面的查询如果where指定匹配条件a,b,c的话 查询就走索引.

SQL> set autotrace on

SQL> select *from empl where A=1 and B=2 and c=3;

        A         B         C D

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

        1         2         3 sam

        1         2         3 sam

        1         2         3 sam

Execution Plan

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

  0     SELECT STATEMENT ptimizer=CHOOSE

  1   0  TABLE ACCESS (BY INDEX ROWID) OF 'EMPL'

  2   1    INDEX (RANGE SCAN) OF 'EM_DX' (NON-UNIQUE)

然后我们只指定where的匹配条件 为非复合索引的非主索引键B遮时查询是全表扫描.

SQL> select *from empl where b=2;

        A         B         C D

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

        1         2         3 sam

        1         2         3 sam

        1         2         3 sam

        3         2         2 sam

        3         2         2 sam

        3         2         2 sam

6 rows selected.

Execution Plan