oracle组合索引整理

最近刚好又聊到了组合索引，由于担心自己记忆的不准确就实践了一番，结果发现和百度的好像不太一样，对此有疑惑的我，于是写下了这篇文章，希望对大家有用。

第一种情况：组合索引（主键）

1、建表

-- CREATE TABLE
CREATE TABLE A
(
  AID   NUMBER NOT NULL,
  ACOU  NUMBER NOT NULL,
  ACOUA CHAR(30) NOT NULL
)
TABLESPACE BDCK
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY  CONSTRAINTS
ALTER TABLE A
  ADD CONSTRAINT PA PRIMARY KEY (AID, ACOU, ACOUA)
  USING INDEX
  TABLESPACE BDCK
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

2、数据生成，我用的PLSQL的数据生成工具

3、索引测试

语句1：使用组合索引全条件查询

SELECT * FROM A WHERE A.AID='3127' AND A.ACOU='35552' AND  A.ACOUA='TaiwanTaoyuan'

执行计划：

 

语句2：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE  '%35552%'

执行计划：

 

语句3：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE  '35552%'

执行计划：

 

语句4：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE  '%TaiwanTaoyuan%'

执行计划：

 

语句5：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE  'TaiwanTaoyuan%'

执行计划：

 

语句6：

SELECT * FROM A WHERE A.ACOU='35552' AND  A.ACOUA='TaiwanTaoyuan'

执行计划：

 

语句7：

SELECT * FROM A WHERE A.ACOU='35552' AND  A.ACOUA='%TaiwanTaoyuan%'

执行计划：

 

语句8：

SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA LIKE  'TaiwanTaoyuan%'

执行计划：

 

语句全部命中索引PA，但6、7、8优化器目标不同产生的结果不同，
以语句6为例限定行数的执行计划如下：

其余优化目标均不走索引！

 

第二种情况：组合索引（非主键）

1、接上表，重新创建索引

-- Drop primary, unique and foreign key constraints
alter table A
  drop constraint PA cascade;
-- Create/Recreate indexes
create index ka on A (aid, acou, acoua);

2、索引测试

语句1：使用组合索引全条件查询

SELECT * FROM A WHERE A.AID='3127' AND A.ACOU='35552' AND  A.ACOUA='TaiwanTaoyuan'

执行计划：

 

语句2：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE  '%35552%'

执行计划：

 

语句3：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOU LIKE  '35552%'

执行计划：

 

语句4：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE  '%TaiwanTaoyuan%'

执行计划：

 

语句5：

SELECT * FROM A WHERE A.AID='3127' AND A.ACOUA LIKE  'TaiwanTaoyuan%'

执行计划：

 

语句6：

SELECT * FROM A WHERE A.ACOU='35552' AND  A.ACOUA='TaiwanTaoyuan'

执行计划：

 

语句7：

SELECT * FROM A WHERE A.ACOU='35552' AND  A.ACOUA='%TaiwanTaoyuan%'

执行计划：

 

语句8：

SELECT * FROM A WHERE A.ACOU='35552' AND A.ACOUA LIKE  'TaiwanTaoyuan%'

执行计划：

 

和组合索引（主键）一样，全部走索引，只是索引扫描类型不一致！

 

结论：

1、组合索引任意字段查询都走索引，和顺序无关

2、查询条件有前缀索引和无前缀索引只影响索引扫描类型，在不同优化器下无前缀，只有在全表以及行数限制的条件下走索引