Oracle中B-Tree、Bitmap和函数索引使用案例总结

目录

一、索引简介

1、索引是一个独立的数据库对象，和数据表table一样。在Oracle中，数据库对象object都是通过段segment结构表示。我们在数据字典dba_segment中，可以使用索引的名称搜索出与segment_name相等的字典项目。

2、索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

3、索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

4、索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

5、索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响

6、索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

7、Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。

8、数据表和索引是可以分开进行存储的。通常，从性能角度考虑我们常将两者放置在不同的Tablespace中，这样做的目的主要是为了分散物理IO。

二、索引原理

1、若没有索引，搜索某个记录时（例如查找name='wjq'）需要搜索所有的记录，因为不能保证只有一个wjq，必须全部搜索一遍

2、若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值按照升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wjq时即可直接查找对应地方

3、创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

三、索引的分类

3.1逻辑分类

3.2物理分类

下面重点讲解B-Tree索引、Bitmap索引和函数索引。

1、B-Tree索引

a、B-Tree索引是Oracle中最常用的索引；B树索引就是一颗二叉树（平衡树），左右两个分支相对平衡；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值。

b、所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

c、能够适应精确查询、模糊查询和比较查询

说明：

Root为根节点，branch为分支节点，leaf到最下面一层称为叶子节点。每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。叶子节点会存放index entry（索引入口），每个索引入口对应一条记录。

Index entry 的组成部分：

Indexentry entry header  存放一些控制信息。

Key column length   　　某一key的长度

Key column value   　　 某一个key 的值

ROWID  　　                指针，具体指向于某一个数据

创建索引

--创建一张测试表，并插入1000行数据
SEIANG@seiang11g>create table tb_test1(id int,sex char(4),name varchar2(30)) tablespace seiang;
Table created.

SEIANG@seiang11g>begin
  2  for i in 1..1000 loop
  3  insert into tb_test1 values(i,'M','wjq'||i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from tb_test1;

  COUNT(*)
----------
      1000

SEIANG@seiang11g>
SEIANG@seiang11g>select * from tb_test1 where rownum<10;

        ID SEX  NAME
---------- ---- ------------------------------
       702 M    wjq702
       703 M    wjq703
       704 M    wjq704
       705 M    wjq705
       706 M    wjq706
       707 M    wjq707
       708 M    wjq708
       709 M    wjq709
       710 M    wjq710

9 rows selected.


--创建B-Tree索引
SEIANG@seiang11g>create index idx_test1_id on tb_test1(id) tablespace wjq_index;

Index created.

SEIANG@seiang11g>select object_name,object_type from user_objects where object_name in ('TB_TEST1','IDX_TEST1_ID');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
IDX_TEST1_ID                   INDEX
TB_TEST1                       TABLE

索引分离于表，作为一个单独的个体存在，除了可以根据单个字段创建索引，也可以根据多列创建索引。Oracle要求创建索引最多不可超过32列。

SEIANG@seiang11g>create index idx_test1_sex_name on tb_test1(sex,name) tablespace wjq_index;

Index created.

SEIANG@seiang11g>
SEIANG@seiang11g>select object_name,object_type from user_objects where object_name in ('TB_TEST1','IDX_TEST1_ID','IDX_TEST1_SEX_NAME');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
IDX_TEST1_ID                   INDEX
IDX_TEST1_SEX_NAME             INDEX
TB_TEST1                       TABLE

这里需要理解：

　　编写一本书，只有章节页面定好之后再设置目录；数据库索引也是一样，只有先插入好数据，再建立索引。那么我们后续对数据库的内容进行插入、删除，索引也需要随之变化。但索引的修改是由oracle自动完成的。

上面这张图能更加清晰的描述索引的结构。

根节点记录0至50条数据的位置，分支节点进行拆分记录0至10……42至50，叶子节点记录每第数据的长度和值，并由指针指向具体的数据。最后一层的叶子节是双向链接，它们是被有序的链接起来，这样才能快速锁定一个数据范围。

例如：

SEIANG@seiang11g>select * from tb_test1 where id>23 and id<32;

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2241831008

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     8 |   288 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST1     |     8 |   288 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST1_ID |     8 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">23 AND "ID"<32)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        849  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

如上面查找的列子，通过索引的方式先找到第23条数据，再找到第32条数据，这样就能快速的锁定一个查找的范围，如果每条数据都要从根节点开始查找的话，那么效率就会非常低下。

2、Bitmap索引

a、创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换

b、位图索引主要针对大量相同值的列而创建。拿全国居民登录表来说，假设有四个字段：姓名、性别、年龄、和身份证号，年龄和性别两个字段会产生许多相同的值，性别只有男女两种取值，年龄，1到120（假设最大年龄120岁）个值。那么不管一张表有几亿条记录，但根据性别字段来区分的话，只有两种取值（男、女）。那么位图索引就是根据字段的这个特性所建立的一种索引。

c、对于基数小的列适合简历位图索引（例如性别等）

从上图，我们可以看出，一个叶子节点（用不同颜色标识）代表一个key, start rowid和end rowid规定这种类型的检索范围，一个叶子节点标记一个唯一的bitmap值。因为一个数值类型对应一个节点，当进行查询时，位图索引通过不同位图取值直接的位运算（与或），来获取到结果集合向量（计算出的结果）。

举例讲解：

假设存在数据表T，有两个数据列A和B，取值如下，我们看到A和B列中存在相同的数据。

对两个数据列A、B分别建立位图索引：idx_t_bita和idx_t_bitb。两个索引对应的存储逻辑结构如下：

Idx_t_bita索引结构，对应的是叶子节点：

Idx_t_bitb索引结构，对应的是叶子节点：

对查询“select * from t where b=1 and (a=’L’ or a=’M’)”

分析：

位图索引使用方面，和B*索引有很大的不同。B*索引的使用，通常是从根节点开始，经过不断的分支节点比较到最近的符合条件叶子节点。通过叶子节点上的不断scan操作，“扫描”出结果集合rowid。

而位图索引的工作方式截然不同。通过不同位图取值直接的位运算（与或），来获取到结果集合向量（计算出的结果）。

针对实例SQL，可以拆分成如下的操作：

1、a=’L’ or a=’M’

a=L：向量：1010

a=M：向量：0001

or操作的结果，就是两个向量的或操作：结果为1011。

2、结合b=1的向量

中间结果向量：1011

B=1：向量：1001

and操作的结果，1001。翻译过来就是第一和第四行是查询结果。

3、获取到结果rowid

目前知道了起始rowid和终止rowid，以及第一行和第四行为操作结果。可以通过试算的方法获取到结果集合rowid。

位图索引的特点：

（1）Bitmap索引的存储空间节省 

（2）Bitmap索引创建的速度快

（3）Bitmap索引允许键值为空 

（4）Bitmap索引对表记录的高效访问

创建Bitmap索引

--接着上面B-Tree索引所创建的表tb_test1为例，基于该表来创建Bitmap索引
对于上面表来说sex（性别）只有两种值，最适合用来创建位图所引
SEIANG@seiang11g>create bitmap index bitmap_idx_test1_sex on tb_test1(sex) tablespace wjq_index;

Index created.

SEIANG@seiang11g>select object_name,object_type from user_objects where object_name in ('TB_TEST1','BITMAP_IDX_TEST1_SEX');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
BITMAP_IDX_TEST1_SEX           INDEX
TB_TEST1                       TABLE

SEIANG@seiang11g>select * from tb_test1 where sex='M';

1000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2608569169

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |  1000 | 36000 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TB_TEST1             |  1000 | 36000 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                      |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BITMAP_IDX_TEST1_SEX |       |       |            |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SEX"='M')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         82  consistent gets
          0  physical reads
          0  redo size
      33757  bytes sent via SQL*Net to client
       1249  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

--查看表tb_test1上的所有创建的索引及类型
SEIANG@seiang11g>select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS
  2  from user_ind_columns a,user_indexes b
  3  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME='TB_TEST1';

INDEX_NAME                     INDEX_TYPE           TABLE_NAME                     COLUMN_NAME          STATUS
------------------------------ -------------------- ------------------------------ -------------------- --------
IDX_TEST1_ID                   NORMAL               TB_TEST1                       ID                   VALID
IDX_TEST1_SEX_NAME             NORMAL               TB_TEST1                       SEX                  VALID
IDX_TEST1_SEX_NAME             NORMAL               TB_TEST1                       NAME                 VALID
BITMAP_IDX_TEST1_SEX           BITMAP               TB_TEST1                       SEX                  VALID

3、函数索引

a、当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度

b、函数索引既可以使用B-Tree索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引

c、函数索引中可以使用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等

d、函数索引有一点要特别注意，在使用函数索引的时候，SQL语句中的对应表达式必须与创建函数索引的表达式完全一致（当然，空格、关键字大小写的可以忽略），如果不是完全一致，则也利用不上函数索引。

创建函数索引

--创建一张测试表tb_test2，同时插入相应的数据
SEIANG@seiang11g>create table tb_test2 as select * from dba_objects where owner in ('SYS','BI','SCOTT','PUBLIC','SYSTEM');

Table created.

SEIANG@seiang11g>
SEIANG@seiang11g>select owner,count(*) from tb_test2 group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
PUBLIC                              34002
SYSTEM                                618
SCOTT                                  25
BI                                      8
SYS                                 37803

SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from tb_test2;

  COUNT(*)
----------
     72456

--为owner列创建普通的B-Tree索引
SEIANG@seiang11g>create index idx_test2_owner on tb_test2(owner);

Index created.


--利用索引列，针对列值为BI，进行普通查询；与预想一样，这里用到了索引扫描
SEIANG@seiang11g>select * from tb_test2 where owner='BI';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1141247240

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     8 |  1656 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST2        |     8 |  1656 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST2_OWNER |     8 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='BI')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         23  recursive calls
          0  db block gets
         99  consistent gets
          1  physical reads
          0  redo size
       2238  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed


--清空buffer_cache缓冲区，避免影响后续操作对于物理读的观察。
SEIANG@seiang11g>alter system flush buffer_cache;

System altered.

--使用UPPER函数进行条件过滤，并观察执行计划，通过执行计划，可以明显看出，未使用索引扫描，进而导致大量的物理读操作。
SEIANG@seiang11g>select * from tb_test2 where upper(owner)='BI';

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2703936182

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    12 |  2484 |   290   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TB_TEST2 |    12 |  2484 |   290   (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("OWNER")='BI')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       1106  consistent gets
       1039  physical reads
          0  redo size
       1854  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

通过上面的示例可以看到，即使条件列建立了索引，当索引列上使用函数进行条件匹配，执行计划将不会选择索引扫描。

--在索引列上建立函数索引
SEIANG@seiang11g>create index func_idx_test2_owner on tb_test2(UPPER(owner));

Index created.

--查看并验证建立的函数索引
需要注意的，由于此索引是基于函数建立的，因此columns一列无法显示真正的列名，可以通过user_ind_expressions视图查看

SEIANG@seiang11g>select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS
  2  from user_ind_columns a,user_indexes b
  3  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME='TB_TEST2';

INDEX_NAME                     INDEX_TYPE                TABLE_NAME                     COLUMN_NAME          STATUS
------------------------------ ------------------------- ------------------------------ -------------------- --------
IDX_TEST2_OWNER                NORMAL                    TB_TEST2                       OWNER                VALID
FUNC_IDX_TEST2_OWNER           FUNCTION-BASED NORMAL     TB_TEST2                       SYS_NC00016$         VALID

SEIANG@seiang11g>select * from user_ind_expressions where INDEX_NAME='FUNC_IDX_TEST2_OWNER';

INDEX_NAME                     TABLE_NAME                     COLUMN_EXPRESSION              COLUMN_POSITION
------------------------------ ------------------------------ ------------------------------ ---------------
FUNC_IDX_TEST2_OWNER           TB_TEST2                       UPPER("OWNER")                               1

--再次使用UPPER函数进行条件查询，此时执行计划使用索引扫描，进而物理读明显降低。
SEIANG@seiang11g>alter system flush buffer_cache;

System altered.
SEIANG@seiang11g>select * from tb_test2 where upper(owner)='BI';

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617808431

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     8 |  1792 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST2             |     8 |  1792 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FUNC_IDX_TEST2_OWNER |     8 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("OWNER")='BI')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         74  consistent gets
        296  physical reads
          0  redo size
       1854  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

通过上面的示例可以看到，由于建立了函数索引，执行计划重新选择了索引扫描，物理读（physical reads）明显降低。

四、索引的常见操作

4.1创建索引

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                   --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

4.2修改索引

（1）重命名索引

alter index [index_name] rename to bitmap_index;

（2）改变索引

alter index [index_name] storage(next 400K maxextents 100);

索引创建后，感觉不合理，也可以对其参数进行修改。详情查看相关文档

（3）调整索引的空间

--新增加空间

alter index [index_name] allocate extent (size 200K datafile '/disk6/index01.dbf');

--释放空间

alter index [index_name] deallocate unused;

索引在使用的过程中可能会出现空间不足或空间浪费的情况，这个时候需要新增或释放空间。上面两条命令完成新增与释放操作。关于空间的新增oracle可以自动帮助，如果了解数据库的情况下手动增加可以提高性能。

（4）重新创建索引

索引是由oracle自动完成，当我们对数据库频繁的操作时，索引也会跟着进行修改，当我们在数据库中删除一条记录时，对应的索引中并没有把相应的索引只是做一个删除标记，但它依然占据着空间。除非一个块中所有的标记全被删除的时，整个块的空间才会被释放。这样时间久了，索引的性能就会下降。这个时候可以重新建立一个干净的索引来提高效率。

alter index [index_name] rebuild tablespace [tablespace_name];

通过上面的命令就可以重现建立一个索引，oracle重建立索引的过程：

1、锁表，锁表之后其他人就不能对表做任何操作。

2、创建新的（干净的）临时索引。

3、把老的索引删除掉

4、把新的索引重新命名为老索引的名字

5、对表进行解锁。

（5）移动索引

其实，我们移动索引到其它表空间也同样使用上面的命令，在指定表空间时指定不同的表空间。新的索引创建在别位置，把老的干掉，就相当于移动了。

alter index [index_name] rebuild tablespace [tablespace_name];

（6）在线重新创建索引：

上面介绍，在创建索引的时候，表是被锁定，不能被使用。对于一个大表，重新创建索引所需要的时间较长，为了满足用户对表操作的需求，就产生的这种在线重新创建索引。

alter index [index_name]  rebuild  online;

创建过程：

1、锁住表

2、创建立临时的和空的索引和IOT表用来存在on-going DML。普通表存放的键值，IOT所引表直接存放的表中数据；on-gong DML也就是用户所做的一些增删改的操作。

3、对表进行解锁

4、从老的索引创建一个新的索引。

5、IOT表里存放的是on-going DML信息,IOT表的内容与新创建的索引合并。

6、锁住表

7、再次将IOT表的内容更新到新索引中，把老的索引干掉。

8、把新的索引重新命名为老索引的名字

9、对表进行解锁

（7）合并索引

表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较）

如上图，在很多索引中有剩余的空间，可以通过一个命令把剩余空间整合到一起。

alter index [index_name] coalesce;

（8）查看索引

select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS from user_ind_columns a,user_indexes b where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME='XXX';

--查看函数索引的详细定义

select * from user_ind_expressions where INDEX_NAME='FUNC_IDX_TEST2_OWNER';

（9）分析索引

检查所引的有效果，前面介绍，索引用的时间久了会产生大量的碎片、垃圾信息与浪费的剩余空间了。可以通过重新创建索引来提高所引的性能。

可以通过一条命令来完成分析索引，分析的结果会存放在在index_stats表中。

SEIANG@seiang11g>select count(*) from index_stats;

  COUNT(*)
----------
         0

--删除200行数据
SEIANG@seiang11g>delete tb_test1 where id>800;

200 rows deleted.

--进行索引分析
SEIANG@seiang11g>analyze index IDX_TEST1_ID validate structure;

Index analyzed.

SEIANG@seiang11g>select count(*) from index_stats;

  COUNT(*)
----------
         1

SEIANG@seiang11g>
SEIANG@seiang11g>select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

    HEIGHT NAME                              LF_ROWS    LF_BLKS DEL_LF_ROWS
---------- ------------------------------ ---------- ---------- -----------
         2 IDX_TEST1_ID                         1000          3         200

说明：

（HEIGHT）这个所引高度是2，（NAME）索引名为IDX_TEST1_ID，（LF_ROWS）所引表有1000行数据，（LF_BLKS）占用3个块，（DEL_LF_ROWS）删除200条记录。

　　这里也验证了前面所说的一个问题，删除的200条数据只是标记为删除，因为总的数据条数依然为1000条，占用3个块，那么每个块大于333条记录，只有删除的数据大于333条记录，这时一个块被清空，总的数据条数才会减少。

（10）监控索引

无论是投产之后还是开发测试中，我们都在数据表中加入了一些索引。通常我们是不能实时监视每条语句的执行计划，那么在oracle中，可以借助monitoring usage关键字和v$object_usage视图实现这个功能，发现一些不常用的索引，定位优化目标。

--启用监控功能并且收集监控结果。
SEIANG@seiang11g>alter index IDX_TEST1_ID monitoring usage;

Index altered.

SEIANG@seiang11g>select * from v$object_usage where index_name='IDX_TEST1_ID';

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_TEST1_ID                   TB_TEST1                       YES NO  11/09/2017 14:18:02

SEIANG@seiang11g>select * from tb_test1 where id<10;

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2241831008

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     9 |   324 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST1     |     9 |   324 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST1_ID |     9 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<10)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         41  recursive calls
          3  db block gets
         58  consistent gets
          5  physical reads
        548  redo size
        859  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          9  rows processed

SEIANG@seiang11g>select * from v$object_usage where index_name='IDX_TEST1_ID';

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_TEST1_ID                   TB_TEST1                       YES YES 11/09/2017 14:18:02


--关闭索引监控功能
SEIANG@seiang11g>alter index IDX_TEST1_ID nomonitoring usage;

Index altered.

SEIANG@seiang11g>select * from v$object_usage where index_name='IDX_TEST1_ID';

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_TEST1_ID                   TB_TEST1                       NO  YES 11/09/2017 14:18:02 11/09/2017 14:21:34

（11）删除索引

drop index [index_name];

扩展补充：常用的oracle索引视图

较为重要的oracle索引视图如下：
dba_indexes
user_indexes
dba_ind_columns
user_indexes
dba_expressions
user_expressions

说明：
dba_indexes与user_indexes视图，主要涵盖了索引的参数、状态以及关联的表信息，但不包含具体的列信息。
dba_ind_columns与user_ind_columns视图，主要涉及具体的索引列的信息。
dba_expressions与user_expressions视图，主要针对函数索引，可以查看具体的函数信息。

五、索引建立原则总结

1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。

2、如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

3、把索引与对应的表放在不同的表空间。当读取一个表的时候，表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。这样做的目的主要是分散物理IO

4、最好使用一样大小是块。Oracle默认五块，读一次I/O，如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。

5、如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。

6、建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面，因为SQL执行是从后往前的。（小李飛菜刀）

7、至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

8、小表不要简历索引

9、对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引

10、列中有很多空值，但经常查询该列上非空记录时应该建立索引

11、经常进行连接查询的列应该创建索引

12、使用create index时要将最常查询的列放在最前面

13、限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

六、SQL语句不走索引的情况

1、通配符在搜索词首出现时，oracle不能使用索引；

--我们在name上创建索引；
create index index_name on student('name');

--下面的方式oracle不适用name索引
select * from student where name like '%wjq%';

--如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：
select * from student where name like 'wjq%';

2、不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描）

select * from student where not (score=100);
select * from student where score <> 100;

--替换为
select * from student where score>100 or score <100

3、索引上使用空值比较将停止使用索引；

select * from student where score is not null;

关于更多SQL语句不走索引的情况请参考博客：【Oracle index】SQL语句无法走索引的一些情况分析及语句改写思路 ：http://blog.chinaunix.net/uid-7655508-id-3637972.html

参考链接：

http://www.cnblogs.com/wishyouhappy/p/3681771.html

http://www.cnblogs.com/fnng/archive/2012/10/10/2719221.html

 

作者：SEian.G（苦练七十二变，笑对八十一难）