复合索引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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92530/viewspace-246138/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/92530/viewspace-246138/