ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 组合索引的前导列与查询——ORACLE

组合索引的前导列与查询——ORACLE

原创 Linux操作系统 作者:听海★蓝心梦 时间:2009-03-13 09:30:14 0 删除 编辑
关于组合索引的使用一直都存在着一些争议,记得在学OU的Performance Tuning时,讲师认为组合索引的使用存在着一定的局限,只有在谓词中出现全部索引列时才能使用效率最高的index unique scan, 否则谓词中必须包含前导列,否则会走Index full scan或者FTS。

kl@k02> create table te_emp as select * from hr.employees;

Table created.

kl@k02> create index emp_id1 on te_emp(employee_id,JOB_ID, department_id);

Index created.

kl@k02> create index emp_id2 on te_emp(salary);

Index created.

kl@k02> analyze table te_emp compute statistics for table for all columns for all indexes;

Table analyzed.

kl@k02> set autotrace traceonly exp;

kl@k02> select employee_id,JOB_ID, department_id from te_emp;
(不加谓词,走EMP_ID1的FULL SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=107 Bytes=1391)
   1    0   INDEX (FULL SCAN) OF 'EMP_ID1' (NON-UNIQUE) (Cost=1 Card=107 Bytes=1391)

kl@k02> select employee_id,JOB_ID, department_id from te_emp where employee_id=9293;
(加谓词前导列Employee_id时,走EMP_ID1 range scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=13)

kl@k02> select employee_id,JOB_ID, department_id from te_emp where employee_id=1334 and JOB_ID=435 and department_id=273;
(如谓词中包含全部索引列,走EMP_ID1 Range scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID1' (UNIQUE) (Cost=1 Card=1 Bytes=13)


kl@k02> set autotrace off;
kl@k02> select count(*) from (select distinct employee_id from te_emp);

COUNT(*)
----------
       107

kl@k02> select count(*) from te_emp;

COUNT(*)
----------
       107

kl@k02> create index emp_id3 on te_emp(employee_id);
(此时创建一个单列索引emp_id3, 目前组合索引emp_id1仍然存在)
Index created.

kl@k02> analyze table te_emp compute statistics for table for all columns for all indexes;

Table analyzed.

kl@k02> select employee_id from te_emp where employee_id=107;
(并没有走emp_id3, 而是emp_id1的INDEX RANGE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID1' (UNIQUE) (Cost=1 Card=1 Bytes=3)

kl@k02> drop index emp_id1;

Index dropped.

kl@k02> select employee_id from te_emp where employee_id=107;
(drop原来的联合索引,用到了emp_id3,但是还是INDEX RANGE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID3' (NON-UNIQUE) (Cost=1 Card=1 Bytes=3)

(现在测试一下,如果组合index是unique index,会如何?)

kl@k02> drop index emp_id3;

Index dropped.

kl@k02> create unique index emp_id1 on te_emp(employee_id,JOB_ID, department_id);

Index created.

kl@k02> analyze table te_emp compute statistics for table for all columns for all indexes;

Table analyzed.

kl@k02> select * from te_emp where employee_id=107 and JOB_ID='ENGINEER' and department_id=10;
(此时走了unique scan,也就是说只有这种情况联合索引的作用才发挥出来)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=1 Card=1 Bytes=62)
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_ID1' (UNIQUE)

kl@k02> select * from te_emp where employee_id=107;
(此时谓词内只有前导列,不会走unique scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=2 Card=1 Bytes=62)
   2    1     INDEX (RANGE SCAN) OF 'EMP_ID1' (NON-UNIQUE) (Cost=1 Card=1)

kl@k02> select * from te_emp where employee_id=107 and JOB_ID=999 and department_id=10;
(JOB_ID应该是Varchar型的,结果导致整个查询无法走Unique scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=2 Card=1 Bytes=62)
   2    1     INDEX (RANGE SCAN) OF 'EMP_ID1' (UNIQUE) (Cost=1 Card=1)

kl@k02> select * from te_emp where employee_id='TEST' and JOB_ID='TEST' and department_id=10;
(如果前导列类型出错了,索引扫描类型还是一样,UNIQUE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=1 Card=1 Bytes=62)
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_ID1' (UNIQUE)



总结,针对组合索引,比较适宜的情况是组合列唯一,这种情况下对组合列建组合UNIQUE索引受益最大,一次索引扫描就可以了。但如果其中非前导列类型出错,索引UNIQUE扫描将变成RANGE SCAN; 有趣的是如果前导列类型不对,甚至为null,都不影响组合索引的UNIQUE SCAN.

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

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    256
  • 访问量
    1195292