这个是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