ORACLE 索引详解

第一章索引的定义-----------------------------------------------------2

第二章索引的分类-----------------------------------------------------2

第三章 B-Tree索引详解-------------------------------------------------2

第四章位图索引详解----------------------------------------------------7

第五章创建索引的规则-------------------------------------------------14

第六章创建索引时的可选项---------------------------------------------15

第七章索引的维护-----------------------------------------------------16

第八章索引常见操作---------------------------------------------------13

第九章分析索引-------------------------------------------------------26

第一章索引的定义

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的 SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。

第二章索引的分类

1 索引逻辑分类

单列索引：基于一列的操作

多列索引：组合索引，最多为32列。组合索引的列不一定与表中列顺序相同。

惟一索引：列的值各不相同。

非惟一索引：列的值允许相同。

基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。

2 索引物理分类

分区索引

非分区索引：非分区既可以是B－树，也可以是位图索引。

B-树索引：包括正常索引或反转关键字索引

位图索引

第三章 B-Tree 索引详解

1 结构

B-Tree index 也是我们传统上常见所理解的索引。 B-tree （balance tree）即平衡树，左右两个分支相对平衡。

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

Index entry 的组成部分：

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

Key column length 　　某一key的长度

Key column value 　　某一个key 的值

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

2 场合：非常适合数据重复度低的字段例如身份证号码手机号码 QQ号等字段，常用于主键唯一约束，一般在在线交易的项目中用到的多些。

3 原理：一个键值对应一行（rowid）格式：【索引头|键值|rowid】

4 优点：当没有索引的时候，oracle只能全表扫描where qq=40354446 这个条件那么这样是灰常灰常耗时的，当数据量很大的时候简直会让人崩溃，那么有个B-tree索引我们就像翻书目录一样，直接定位rowid立刻就找到了我们想要的数据，实质减少了I/O操作就提高速度，它有一个显著特点查询性能与表中数据量无关，例如查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。

当我们的字段中使用了主键or唯一约束时，不用想直接可以用B-tree索引

5 缺点：不适合键值重复率较高的字段上使用，例如第一章 1-500page 第二章 501-1000page

实验：

1 清空共享池，数据库缓冲区

SQL> alter system flush shared_pool;

SQL> alter system flush buffer_cache;

2 创建表t1,t2

T1表中没有重复值

SQL> create table t1 as select object_id,object_name from dba_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)

----------

72476

SQL> select * from t1 where rownum<=5;

OBJECT_ID OBJECT_NAME

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

20 ICOL$

46 I_USER1

28 CON$

15 UNDO$

29 C_COBJ#

t2 表的object_id列我们是做了取余操作，值就只有0,1两种，因此重复率较高，如此设置为了说明重复率对B树索引的影响；

SQL> create table t2 as select mod(object_id,2) object_id,object_name from dba_objects;

SQL> select * from t2 where rownum<=5;

OBJECT_ID OBJECT_NAME

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

0 ICOL$

0 I_USER1

0 CON$

1 UNDO$

1 C_COBJ#

3 创建索引

SQL> create index t1_index on t1(object_id);

SQL> create index t2_index on t2(object_id);

4 查看t1,t2的重复率

SQL> select count(distinct(object_id)) from t1;

COUNT(DISTINCT(OBJECT_ID))

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

72476

SQL> select count(distinct(object_id)) from t2;

COUNT(DISTINCT(OBJECT_ID))

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

2

5 收集表的统计信息

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',method_opt=>'for all indexed columns size 2',cascade=>TRUE);

显示执行计划和统计信息+设置autotrace简介

序号命令解释

1 SET AUTOTRACE OFF 此为默认值，即关闭Autotrace

2 SET AUTOTRACE ON EXPLAIN 只显示执行计划

3 SET AUTOTRACE ON STATISTICS 只显示执行的统计信息

4 SET AUTOTRACE ON 包含2,3两项内容

5 SET AUTOTRACE TRACEONLY 与ON相似，但不显示语句的执行结果

T1（重复率低的表）

SQL> set autotrace on explain

SQL> select * from t1 where object_id=1;

Execution Plan

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

Plan hash value: 1186333541

TABLE ACCESS BY INDEX ROWID| T1 -------------走it_index索引了

INDEX RANGE SCAN | T1_INDEX

Statistics

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

2 consistent gets

t1表的object_id没有重复值，因此使用B-tree索引扫描只有2次一致性读

T2(重复率高的表)

SQL> select * from t2 where object_id=1;

Execution Plan

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

Plan hash value: 1513984157

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

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

| 0 | SELECT STATEMENT | | 36199 | 954K| 90 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T2 | 36199 | 954K| 90 (2)| 00:00:02 |

2739 consistent gets

T2为什么要用全表扫描而不用B-tree索引呢？这是因为oracle基于成本优化器CBO认为使用全表扫描要比使用B-tree索引性能更好更快，由于我们结果重复率很高，导致有2739次一致性读，从cup使用率90%上看也说明了B-tree索引不适合键值重复率较高的列

我们在看一下 强制使用B-tree索引时，效率是不是没有全表扫描高呢？

SQL> select /*+index(t2 t2_index) */ * from t2 where object_ID=1;

Execution Plan

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

Plan hash value: 1666064008

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

--------

me |

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

--------

| 0 | SELECT STATEMENT | | 36199 | 954K| 391 (1)| 00

:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 36199 | 954K| 391 (1)| 00

:00:05 |

|* 2 | INDEX RANGE SCAN | T2_INDEX | 36199 | | 69 (0)| 00

:00:01 |

5220 consistent gets

使用B-tree索引5220次一致性读 > 全表扫描2739次一致性读，而且cpu使用率也非常高，显然效果没有全表扫描高

小结：从以上的测试我们可以了解到，B-tree索引在什么情况下使用跟键值重复率高低有很大关系的，之间没有一个明确的分水岭，只能多测试分析执行计划后来决定。

第四章位图索引详解

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

Bitmap Index

2 场合：列的基数很少，可枚举，重复值很多，数据不会被经常更新

3 原理：一个键值对应很多行（rowid），格式：键值 start_rowid end_rowid 位图

4 优点：OLAP 例如报表类数据库重复率高的数据特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果

位图索引的特点：

1.Bitmap索引的存储空间节省

2.Bitmap索引创建的速度快

3.Bitmap索引允许键值为空

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

5 缺点：不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改

一个键值，会影响同键值的多行，所以对于OLTP 系统位图索引基本上是不适用的

举例讲解：

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

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

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

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

实验：位图索引与B-tree索引性能比较

利用dba_objects创建两个相同行数据的表t3,t4

SQL> create table t3 as select * from dba_objects;

SQL> insert into t3 select * from t3;

72480 rows created.

/

144960 rows created.

因object_type字段重复值较高，顾在此字段上创建bitmap索引

SQL> create bitmap index t3_index on t3(object_type);

创建一个和t3表结构一模一样的表t4，并在object_type列上创建一个B-tree索引

SQL> create table t4 as select * from t3;

在t4表object_type创建B-tree索引

SQL> create index t4_index on t4(object_type);

对比位图索引和B-tree 索引所占空间大小,很明显位图要远远小于B-tree索引所占用的空间，节约空间特性也是我们选择位图的理由之一

SQL> select segment_name,bytes/1024/1024||'M' bytes from user_segments where segment_type='INDEX';

SEGMENT_NAME BYTES

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

T1_INDEX 2M

T2_INDEX 2M

T3_INDEX .1875M

T4_INDEX 7M

显示执行计划

SQL> set autotrace trace exp stat;

SQL> select count(*) from t3 where object_type='TABLE';

Execution Plan

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

Plan hash value: 3466593685

me |

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

--------

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

:00:01 |

| 1 | SORT AGGREGATE | | 1 | 11 | |

|

| 2 | BITMAP CONVERSION COUNT | | 6401 | 70411 | 1 (0)| 00

:00:01 |

|

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

--------

Predicate Information (identified by operation id):

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

3 - access("OBJECT_TYPE"='TABLE')

Note

-----

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

Statistics

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

9 recursive calls

0 db block gets

83 consistent gets

1 physical reads

0 redo size

528 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)

1 rows processed

在创建有B-tree索引的表上做count操作对比执行计划

SQL> select count(*) from t4 where object_type='TABLE';

Execution Plan

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

Plan hash value: 1074473071

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

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

| 0 | SELECT STATEMENT | | 1 | 11 | 35 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 11 | | |

|* 2 | INDEX RANGE SCAN| T4_INDEX | 12431 | 133K| 35 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_TYPE"='TABLE')

Note

-----

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

Statistics

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

9 recursive calls

0 db block gets

114 consistent gets 一致性读的次数高于位图索引表的次数，位图索引效率高

29 physical reads

0 redo size

528 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)

1 rows processed

我们再看看等值查找where object_type='TABLE'情况下位图索引和B-tree索引的性能对比

SQL> select * from t3 where object_type='TABLE' ;

11424 rows selected.

Execution Plan

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

Plan hash value: 3749216620

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

---------

ime |

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

---------

| 0 | SELECT STATEMENT | | 6401 | 1293K| 508 (0)| 0

0:00:07 |

| 1 | TABLE ACCESS BY INDEX ROWID | T3 | 6401 | 1293K| 508 (0)| 0

0:00:07 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | |

|

|

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

---------

Predicate Information (identified by operation id):

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

3 - access("OBJECT_TYPE"='TABLE')

Note

-----

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

Statistics

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

7 recursive calls

0 db block gets

1542 consistent gets

0 physical reads

0 redo size

1264161 bytes sent via SQL*Net to client

8894 bytes received via SQL*Net from client

763 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11424 rows processed

t4 表上使用B-tree索引得到执行计划

SQL> select /*+index(t4 t4_index) */ * from t4 where object_type='TABLE' ;

11424 rows selected.

Execution Plan

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

Plan hash value: 81425413

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

--------

me |

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

--------

| 0 | SELECT STATEMENT | | 12431 | 2512K| 529 (0)| 00

:00:07 |

| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 12431 | 2512K| 529 (0)| 00

:00:07 |

|* 2 | INDEX RANGE SCAN | T4_INDEX | 12431 | | 35 (0)| 00

:00:01 |

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

--------

Predicate Information (identified by operation id):

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

2 - access("OBJECT_TYPE"='TABLE')

Note

-----

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

Statistics

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

290 recursive calls

0 db block gets

2363 consistent gets

0 physical reads

0 redo size

1264161 bytes sent via SQL*Net to client

8894 bytes received via SQL*Net from client

763 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11424 rows processed

小结：在等值查找中我们可以看出位图索引的效率依言高于B-tree索引

第五章创建索引的规则

1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。

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

2、把索引与对应的表放在不同的表空间。

当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。

3、最好使用一样大小是块。

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

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

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

第六章创建索引时的可选项

oracle中建索引可能大家都会，但是建索引是有几个选项参数却很少有人关注，在某些特殊环境下，可能会非常有用，下面一一说明：

1.NOSORT，记录排序可选项。

默认情况下，在表中创建索引的时候，会对表中的记录进行排序，排序成功后再创建索引。但是当记录比较多的是，这个排序作业会占用比较多的时间。

特殊情况下，我们就可以使用该参数加快建索引的速度。

例如：

create index idx_scm_salaud_bill_hdr_relid
on SCM_SALAUD_BILL_HDR (audrelateid) nosort ;

2.online ,在线建索引

在生产环境中建索引时，经常碰到：“数据表处于busy状态，需要加nowait参数”的提示。

因为：默认情况下，数据库系统是不允许DML操作与创建索引的操作同时进行的，例如上文的索引，在建的同时，不允许有人在SCM_SALAUD_BILL_HDR 上执行update、insert等操作，这个在生产环境中几乎是不可能。

那online选项就派上用场，可以 DML操作与创建索引操作是否可以同时进行，但是代价就是建索引的时间会延长。

create index idx_scm_salaud_bill_hdr_relid
on SCM_SALAUD_BILL_HDR (audrelateid) online;

3.NOLOGGING，是否需要记录日志信息

这个好理解，就是建索引时记不记日志，一般用在在大型表上建索引，使用该参数，默认是记日志。

4.COMPUTE STATISTICS，是否更新统计信息。

数据库在选择执行计划时，依据就是表、索引的统计信息，该参数会提示数据库建索引的同时，更新对应的统计信息。但是在一个数据修改量比较大的环境中，使用该选项有可能导致执行计划的不稳定。

5.PARALLEL，多服务进程创建索引。

数据库服务器若是多CPU情况下，使用该参数会增加并发，提高效率

第七章 Oracle 索引的维护

7.1 查看系统表中的用户索引
在Oracle中，SYSTEM表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。
一般来说，应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦SYSTEM表损坏了，只能重新生成数据库。我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在。

SQL> select owner from dba_indexes where tablespace_name='SYSTEM' and owner not in('SYS','SYSTEM');

OWNER

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

OUTLN

ORDDATA

OLAPSYS

MDSYS

17 rows selected.

7.2 索引的存储情况检查
Oracle为数据库中的所有数据分配逻辑结构空间。数据库空间的单位是block、extent和segment。

Block ：是Oracle使用和分配的最小存储单位。它是由数据库建立时设置的DB_BLOCK_SIZE决定的。一旦数据库生成了，数据块的大小不能改变。要想改变只能重新建立数据库。

Extent ：是由一组连续的block组成的。一个或多个extent组成一个segment。当一个segment中的所有空间被用完时，Oracle为它分配一个新的extent。
Segment ：是由一个或多个extent组成的。它包含某表空间中特定逻辑存储结构的所有数据。一个段中的extent可以是不连续的，甚至可以在不同的数据文件中。

一个object只能对应于一个逻辑存储的segment，我们通过查看该segment中的extent，可以看出相应object的存储情况。
（1）查看索引段中extent的数量：

SQL> SELECT segment_name,COUNT(*) FROM dba_extents WHERE segment_type = 'INDEX' AND owner = UPPER ('SYS') AND SEGMENT_NAME LIKE 'T%' GROUP BY segment_name;

SEGMENT_NAME COUNT(*)

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

T3_INDEX 3

T1_INDEX 17

T2_INDEX 17

T4_INDEX 22

（2）查看表空间内的索引的扩展情况：

SQL> select substr(segment_name,1,20) "segment name",bytes,count(bytes) from dba_extents

where segment_name in (select index_name from dba_indexes where tablespace_name=UPPER('SYS'))

group by segment_name,bytes order by segment_name;

no rows selected

7.3索引的选择性
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。
确定索引的选择性，可以有两种方法：手工测量和自动测量。

（1）手工测量索引的选择性
如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性：
　　列的选择性=不同值的数目/行的总数 /* 越接近1越好 */

select count(distinct 第一列||"%"||第二列)/count(*) from 表名
如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。
手工方法的优点是在创建索引前就能评估索引的选择性。

（2）自动测量索引的选择性
如果分析一个表，也会自动分析所有表的索引。
第一，为了确定一个表的确定性，就要分析表。
analyze table 表名 compute statistics
第二，确定索引里不同关键字的数目：
select distinct_keys from user_indexes where table_name="表名" and index_name="索引名"
第三，确定表中行的总数：
select num_rows from user_tables where table_name="表名"
第四，索引的选择性=索引里不同关键字的数目/表中行的总数：
select i.distinct_keys/t.num_rows from user_indexes i, user_tables t

     where i.table_name="表名" and i.index_name="索引名" and i.table_name=t.table_name
第五，可以查询USER_TAB_COLUMNS以了解每个列的选择性。
    表中所有行在该列的不同值的数目：
    select column_name, num_distinct from user_tab_columns where table_name="表名"

列的选择性=NUM_DISTINCT/表中所有行的总数，查询USER_TAB_COLUMNS有助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。要想测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新分析表。

7.4 确定索引的实际碎片
随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。

（1）利用验证索引命令对索引进行验证。
这将有价值的索引信息填入index_stats表。
validate index 用户名.索引名

或者：

analyze index index_name validate structure;

注意：index_stats只保存最近一次分析的结果

（2）查询index_stats表以确定索引中删除的、未填满的叶子（Leaf）行的百分比和 height 字段。
select name,height, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100) "Frag Percent" from index_stats

（3）如果索引的叶子行的碎片超过10%，或者 index_stats中height > =4, 可以考虑对索引进行重建。
alter index 用户名.索引名 rebuild tablespace 表空间名 storage(initial 初始值 next 扩展值) nologging

/* Formatted on 2010/6/19 21:58:45 (QP5 v5.115.810.9015) */

SELECT 'alter index ' || owner || '.' || segment_name || ' rebuild;'

FROM ( SELECT COUNT ( * ),

owner,

segment_name,

t.tablespace_name

FROM dba_extents t

WHERE t.segment_type = 'INDEX'

AND t.owner NOT IN ('SYS', 'SYSTEM')

GROUP BY owner, segment_name, t.tablespace_name

HAVING COUNT ( * ) > 10

ORDER BY COUNT ( * ) DESC);

（4）如果出于空间或其他考虑，不能重建索引，可以整理索引。
alter index用户名.索引名 coalesce

（5）清除分析信息
analyze index 用户名.索引名 delete statistics

7.5 重建索引
（1）检查需要重建的索引
根据以下几方面进行检查，确定需要重建的索引。

第一，查看SYSTEM表空间中的用户索引
为了避免数据字典的碎片出现，要尽量避免在SYSTEM表空间出现用户的表和索引。

select index_name from dba_indexes where tablespace_name="SYSTEM" and owner not in ("SYS","SYSTEM")

第二，确保用户的表和索引不在同一表空间内
表和索引对象的第一个规则是把表和索引分离。把表和相应的索引建立在不同的表空间中，最好在不同的磁盘上。这样可以避免在数据管理和查询时出现的许多I/O冲突。

SELECT i.owner "OWNER",

i.index_name "INDEX",

t.table_name "TABLE",

i.tablespace_name "TABLESPACE"

FROM dba_indexes i, dba_tables t

WHERE i.owner = t.owner

AND i.table_name = t.table_name

AND i.tablespace_name = t.tablespace_name

AND i.owner NOT IN ('SYS', 'SYSTEM')

第三，查看数据表空间里有哪些索引
用户的默认表空间应该不是SYSTEM表空间，而是数据表空间。在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。这是程序员经常忽略的一个问题。应该在建索引时，明确的指明相应的索引表空间。

SELECT owner, segment_name, SUM (bytes)

FROM dba_segments

WHERE tablespace_name ='SYSTEM' AND segment_type = 'INDEX'

GROUP BY owner, segment_name

第四，查看哪个索引被扩展了超过10次
随着表记录的增加，相应的索引也要增加。如果一个索引的next extent值设置不合理（太小），索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。

（1）查看索引扩展次数

SELECT COUNT ( * ),

owner,

segment_name,

tablespace_name

FROM dba_extents

WHERE segment_type = 'INDEX' AND owner NOT IN ('SYS', 'SYSTEM')

GROUP BY owner, segment_name, tablespace_name

HAVING COUNT ( * ) > 10

ORDER BY COUNT ( * ) DESC

（2）找出需要重建的索引后，需要确定索引的大小，以设置合理的索引存储参数。

SELECT owner "OWNER",

segment_name "INDEX",

tablespace_name "TABLESPACE",

bytes "BYTES/COUNT",

SUM (bytes) "TOTAL BYTES",

ROUND (SUM (bytes) / (1024 * 1024), 0) "TOTAL M",

COUNT (bytes) "TOTAL COUNT"

FROM dba_extents

WHERE segment_type = 'INDEX'

AND segment_name IN ('INDEX_NAME1', 'INDEX_NAME2')

GROUP BY owner,

segment_name,

segment_type,

tablespace_name,

bytes

ORDER BY owner, segment_name

（3）确定索引表空间还有足够的剩余空间
确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。

SELECT ROUND (bytes / (1024 * 1024), 2) "free(M)"

FROM sm$ts_free

WHERE tablespace_name = '表空间名'

（4）重建索引
重建索引时要注意以下几点：
a.如果不指定tablespace名，索引将建在用户的默认表空间。
b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。
c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。

alter index 索引名 rebuild tablespace 索引表空间名 storage(initial 初始值 next 扩展值) nologging

（5）检查索引
对重建好的索引进行检查。
select * from dba_extents where segment_name="索引名"

（6）根据索引进行查询，检查索引是否有效
使用相应的where条件进行查询，确保使用该索引。看看使用索引后的效果如何。
select * from dba_ind_columns where index_name='索引名'

然后，根据相应的索引项进行查询。
select * from "表名" where ...

（7）找出有碎片的表空间，并收集其碎片。
重建索引后，原有的索引被删除，这样会造成表空间的碎片。

SELECT 'alter tablespace ' || tablespace_name || ' coalesce;'

FROM dba_free_space_coalesced

WHERE percent_blocks_coalesced != 100

查看索引占用空间大小：

select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'INDBILLLOG5_CALLEND';

查看表占用空间大小

select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'TBILLLOG5';

整理表空间的碎片。
alter tablespace 表空间名 coalesce

第八章索引常见操作

改变索引：

SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);

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

调整索引的空间：

新增加空间

SQL> alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');

释放空间

SQL> alter index oraers_id_idx deallocate unused;

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

重新创建索引：

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

SQL> alter index orders_region_id_idx rebuild tablespace index02;

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

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

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

3、把老的索引删除掉

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

5、对表进行解锁。

移动所引：

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

SQL> alter index orders_region_id_idx rebuild tablespace index03;

在线重新创建索引：

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

SQL> alter index orders_id_idx 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、对表进行解锁

整合索引碎片：

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

SQL> alter index orders_id_idx coalesce;

删除索引：

SQL> drop index hr.departments_name_idx;

第九章 分析索引

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

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

查看存放分析数据的表：

SQL> select count(*) from index_stats;

COUNT(*)

----------

0

执行分析索引命令：

SQL> analyze index my_bit_idx validate structure;

Index analyzed.

再次查看 index_stats 已经有了一条数据

SQL> select count(*) from index_stats;

COUNT(*)

----------

1

把数据查询出来：

SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS

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

2 MY_BIT_IDX 1000 3 100

分析数据分析：

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

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

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！

oracle 索引详解