• 博客访问: 29675
  • 博文数量: 79
  • 用 户 组: 普通用户
  • 注册时间: 2017-08-25 10:20
  • 认证徽章:
个人简介

喜欢折腾 建立一个学习交流群558043289,有问题可以交流。

文章分类

全部博文(79)

文章存档

2018年(78)

2017年(1)

我的朋友

分类: Oracle

2018-05-24 10:45:45

原文地址:oracle 索引详解 作者:chenoracle

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


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

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

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

|   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


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

--------


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

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

20121010233148310000000000000000000000.png


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

3 原理:一个键值对应很多行(rowid), 格式:键值  start_rowid   end_rowid  位图

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

位图索引的特点

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

2.Bitmap索引创建的速度快

3.Bitmap索引允许键值为空 

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


5 缺点:不适合重复率低的字段,还有经常DML操作(insertupdatedelete),因为位图索引的锁代价极高,修改一个位图索引段影响整个位图段,例如修改

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

举例讲解:

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

http://pic002.cnblogs.com/images/2012/311516/2012101023352768.jpg

对两个数据列AB分别建立位图索引:idx_t_bitaidx_t_bitb。两个索引对应的存储逻辑结构如下:

Idx_t_bita索引结构,对应的是叶子节点:

http://pic002.cnblogs.com/images/2012/311516/2012101023360212.jpg

Idx_t_bitb索引结构,对应的是叶子节点:

http://pic002.cnblogs.com/images/2012/311516/2012101023362858.jpg

实验:位图索引与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;


t4object_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

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

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 |


|*  3 |    BITMAP INDEX SINGLE VALUE| T3_INDEX |       |       |            |

       |


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

--------



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


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

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

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

|   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


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

---------


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

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

        |


|*  3 |    BITMAP INDEX SINGLE VALUE | T3_INDEX |       |       |            |

        |


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

---------



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


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

--------


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

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 上执行updateinsert等操作,这个在生产环境中几乎是不可能。

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

OUTLN

OUTLN

OUTLN

OUTLN

ORDDATA

ORDDATA

ORDDATA

OLAPSYS

OLAPSYS

MDSYS

MDSYS

MDSYS

MDSYS

MDSYS

MDSYS

MDSYS


17 rows selected.



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

BlockOracle使用和分配的最小存储单位。它是由数据库建立时设置的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、对表进行解锁


整合索引碎片

http://pic002.cnblogs.com/images/2012/311516/2012101023450440.png

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

SQL> alter index orders_id_idx  coalesce;


删除索引

SQL> drop  index  hr.departments_name_idx;



 

 

 

 

 

 

 

 

 

 

第九章 分析索引


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

http://pic002.cnblogs.com/images/2012/311516/2012101023465918.png

可以通过一条命令来完成分析索引,分析的结果会存放在在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条记录,这时一个块被清空,总的数据条数才会减少。

 

 

阅读(4) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册