oracle 索引概述

一. 概念

（1） 类似书的目录结构，可以提高数据检索的速度

（2） 索引直接指向包含所查询值的行的位置，减少磁盘I/O

（3） 索引与表在物理上独立，Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引

（4） 建立索引时，要考虑好索引的存储开销与性能上的获益之间的比重，不要盲目建立索引

二.分类

1.唯一索引

当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

唯一索引可为空，但不为空时，值需都不相同。

语法：CREATE UNIQUE INDEX index_name ON table (column);

2.组合索引

当两个或多个列经常一起出现在where条件中时，则在这些列上创建组合索引

（1）RBO 下 where 子句为非前导列

rbo下where为非前导列时，执行计划不走索引。

SQL> explain plan for select /*+rule*/* from t1 where c2=1;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3617692013

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

| Id  | Operation         | Name |

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

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| T1   |

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

Predicate Information (identified by operation id):

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

   1 - filter("C2"=1)

Note

-----

   - rule based optimizer used (consider using cbo)

17 rows selected

（2）CBO 下 where 子句为非前导列

从9i起，oracle在cbo下引入了索引跳跃扫描（index skip scan），即使SQL语句的where子句中没有组合索引的前导列，也能使用该索引。

SQL> explain plan for select * from t1 where c2=2;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3617692013

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    26 |     6   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     6   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("C2"=2)

Note

-----

   - dynamic sampling used for this statement

17 rows selected

10053事件跟踪sql，部分信息如下：

  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  6.40  Resp: 6.40  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2342429
      Resp_io: 6.00  Resp_cpu: 2342429
  Access Path: index (index (FFS))
    Index: INDEX_T1
    resc_io: 8.00  resc_cpu: 1927886
    ix_sel: 0.0000e+000  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  8.33  Resp: 8.33  Degree: 1
      Cost_io: 8.00  Cost_cpu: 1927886
      Resp_io: 8.00  Resp_cpu: 1927886
kkofmx: index filter:"T1"."C2"=2
  Access Path: index (FullScan)
    Index: INDEX_T1
    resc_io: 33.00  resc_cpu: 2235008
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 33.47  Resp: 33.47  Degree: 1
  Best:: AccessPath: TableScan

         Cost: 6.40  Degree: 1  Resp: 6.40  Card: 1.00  Bytes: 0

测试中oracle执行计划有考虑索引时的情况，但全表扫描代价最小，oracle选择全表扫描

3.位图索引

与传统的b-tree 索引不同，位图索引由键值和键值所在的rowid构成，多用于olap。

（1）位图索引dml操作会锁定其他行，b-tree索引只会锁定本行，位图索引不适用于频繁更新的oltp

（2）位图索引允许为null，is null时仍能生效，b-tree索引不可以。

（3）每个位图为表中的每一个记录包含一个位（0或1），1表示包含该值，0表示不包含。用 and or 或in 做键值运算。

e.g

createtable t2(c1 varchar2(10),c2 varchar(10));

create bitmap index bit_index_t2_c1 on t2(c1);

create bitmap index bit_index_t2_c2 on t2(c2);

insert into t2 values( 'a','男' );

insert into t2 values( 'b','女' );

insert into t2 values( 'c','男' );

c1列中数据

a  b   c

1  0   0

0  1   0

0   0  1

c2列中数据

男    女

1      0

0      1

1      0

select * from t2 where c1='b' and c2='女' 等价于

b        女

1 and  1   =  1

 (4)适用于相异数据较少的列，如性别，不适用与唯一索引。

4.基于函数的索引

在WHERE条件语句中包含函数或者表达式时,若该字段上有索引，该索引不生效，此时可建立基于此字段的函数索引来提高性能。

函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

e.g

create table t3(c1 varchar2( 10),c2 varchar (10));

insert into t3 values( '1','wuhen' );

insert into t3 values( '2','hello' );

insert into t3 values( '3','world' );

create index index_t3 on t3(c2);

SQL> explain plan for select * from t3 where upper(c2)='WUHEN';

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2574254479

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    14 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T3   |     1 |    14 |     3   (0)| 00:00:01 |

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

此时c2上索引没有被使用。

SQL> drop index index_t3;

Index dropped

创建基于函数的索引

SQL> create index index_t3 on t3(upper(c2));

Index created

SQL> explain plan for select * from t3 where upper(c2)='WUHEN';\

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2208359772

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

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

|   0 | SELECT STATEMENT            |          |     1 |    14 |     1   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T3       |     1 |    14 |     1   (0)| 00

|*  2 |   INDEX RANGE SCAN          | INDEX_T3 |     1 |       |     1   (0)| 00

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

此时c2上的函数索引被使用

5.反向键索引

比如索引值是一个自动增长的列，反向键索引能减少索引层数；多个用户对集中在少数块上的索引行进行修改，容易引起资源的争用，比如对数据块的等待，此时建立方向键索引能提高性能。当范围索引between，>,< 将导致全表扫描。

6.索引组织表(IOT)

将表中的数据按照索 引的结构存储在索引中，提高查询速度。
牺牲插入更新的性能，换取查询 性能。通常用于数据仓库，提供大量的查询，极少的插入修改工作。

必须指定主键。插入数据时，会根据主键列进行B树索引排序，写入磁盘。

7. 分区索引

表可以按range、hash、list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为局部索引和全局索引

局部索引一定是分区索引，分有前缀索引和无前缀索引，常用与olap

全局索引可以依附分区表，也可以是非分区表，但一定是有前缀索引

三.操作

1.创建

alter table 用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

alter table tablename add constraint indexname primary key(column)

create index用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

2.删除 

DROP INDEX index_name；

ALTER TABLE table_name DROP INDEX index_name；

ALTER TABLE table_name DROP PRIMARY KEY；

四.索引失效

1.索引上为null

解决办法，设置not null 或用0代替

2.索引上类型不匹配,隐式数据类型转换

a varchar2

select from where a=1 ---隐式转换，没有用到索引

select from where a='1' --非隐式转换，用到索引

3.索引列上计算,或使用函数

select from where 12*a=12；

4.select from where a like '%abc'；

5.使用！=或 <>

e.g

select * from t1 where t1!='a';

改为

select * from t1 where t1>'a' and t1<'a';