Oracle数据库索引的维护.docx

Oracle数据库索引的维护.docx

《Oracle数据库索引的维护.docx》由会员分享，可在线阅读，更多相关《Oracle数据库索引的维护.docx（25页珍藏版）》请在冰豆网上搜索。

Oracle数据库索引的维护

Oracle数据库索引的维护

发布：

dxy字体：

[增加减小]类型：

转载

-

-

正在看的ORACLE教程是:

Oracle数据库索引的维护。

　本文只讨论Oracle中最常见的索引，即是B-tree索引。

本文中涉及的数据库版本是Oracle8i。

　　一.查看系统表中的用户索引

　　在Oracle中，SYSTEM表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。

　　一般来说，应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。

因为这样会带来数据库维护和管理的很多问题。

一旦SYSTEM表损坏了，只能重新生成数据库。

我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在。

　　二.索引的存储情况检查

　　Oracle为数据库中的所有数据分配逻辑结构空间。

数据库空间的单位是数据块（block）、范围（extent）和段（segment）。

　　Oracle数据块（block）是Oracle使用和分配的最小存储单位。

它是由数据库建立时设置的DB_BLOCK_SIZE决定的。

一旦数据库生成了，数据块的大小不能改变。

要想改变只能重新建立数据库。

（在Oracle9i中有一些不同，不过这不在本文讨论的范围内。

）

　　Extent是由一组连续的block组成的。

一个或多个extent组成一个segment。

当一个segment中的所有空间被用完时，Oracle为它分配一个新的extent。

　　Segment是由一个或多个extent组成的。

它包含某表空间中特定逻辑存储结构的所有数据。

一个段中的extent可以是不连续的，甚至可以在不同的数据文件中。

　　一个object只能对应于一个逻辑存储的segment，我们通过查看该segment中的extent，可以看出相应object的存储情况。

（1）查看索引段中extent的数量：

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

三.索引的选择性

　　索引的选择性是指索引列中不同值的数目与表中记录数的比。

如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。

　　一个索引的选择性越接近于1，这个索引的效率就越高。

　　如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。

如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。

　　确定索引的选择性，可以有两种方法：

手工测量和自动测量。

（1）手工测量索引的选择性

　　如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性：

　　列的选择性=不同值的数目/行的总数/*越接近1越好*/

　　如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。

　　手工方法的优点是在创建索引前就能评估索引的选择性。

（2）自动测量索引的选择性

　　如果分析一个表，也会自动分析所有表的索引。

　　第一，为了确定一个表的确定性，就要分析表。

　　第二，确定索引里不同关键字的数目：

　　第三，确定表中行的总数：

　　第四，索引的选择性=索引里不同关键字的数目/表中行的总数：

　　第五，可以查询USER_TAB_COLUMNS以了解每个列的选择性。

　　表中所有行在该列的不同值的数目：

　　列的选择性=NUM_DISTINCT/表中所有行的总数，查询USER_TAB_COLUMNS有助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。

要想测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新分析表。

　　四.确定索引的实际碎片

　　随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。

插入删除越频繁的表，索引碎片的程度也越高。

碎片的产生使访问和使用该索引的I/O成本增加。

碎片较高的索引必须重建以保持最佳性能。

（1）利用验证索引命令对索引进行验证。

　　这将有价值的索引信息填入index_stats表。

（2）查询index_stats表以确定索引中删除的、未填满的叶子行的百分比。

　　（3）如果索引的叶子行的碎片超过10%，考虑对索引进行重建。

　　（4）如果出于空间或其他考虑，不能重建索引，可以整理索引。

　　（5）清除分析信息

[NextPage]

五.重建索引

（1）检查需要重建的索引。

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

　　第一，查看SYSTEM表空间中的用户索引。

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

　　第二，确保用户的表和索引不在同一表空间内。

　　表和索引对象的第一个规则是把表和索引分离。

把表和相应的索引建立在不同的表空间中，最好在不同的磁盘上。

这样可以避免在数据管理和查询时出现的许多I/O冲突。

　　第三，查看数据表空间里有哪些索引

　　用户的默认表空间应该不是SYSTEM表空间，而是数据表空间。

在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。

这是程序员经常忽略的一个问题。

应该在建索引时，明确的指明相应的索引表空间。

　　第四，查看哪个索引被扩展了超过10次

　　随着表记录的增加，相应的索引也要增加。

如果一个索引的nextextent值设置不合理（太小），索引段的扩展变得很频繁。

索引的extent太多，检索时的速度和效率就会降低。

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

　　（3）确定索引表空间还有足够的剩余空间。

　　确定要把索引重建到哪个索引表空间中。

要保证相应的索引表空间有足够的剩余空间。

　　（4）重建索引。

　　重建索引时要注意以下几点：

　　a.如果不指定tablespace名，索引将建在用户的默认表空间。

　　b.如果不指定nologging，将会写日志，导致速度变慢。

由于索引的重建没有恢复的必要，所以，可以不写日志。

　　c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。

　　（5）检查索引。

　　对重建好的索引进行检查。

　　（6）根据索引进行查询，检查索引是否有效

　　使用相应的where条件进行查询，确保使用该索引。

看看使用索引后的效果如何。

　　然后，根据相应的索引项进行查询。

　　（6）找出有碎片的表空间，并收集其碎片。

　　重建索引后，原有的索引被删除，这样会造成表空间的碎片。

　　整理表空间的碎片。

什么是水线（HighWaterMark）?

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

所有的oracle段（segments，在此，为了理解方便，建议把segment作为表的一个同义词）都有一个在段内容纳数据的上限，我们把这个上限称为"highwatermark"或HWM。

这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。

HWM通常增长的幅度为一次5个数据块，原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使HWM很象一个水库的历史最高水位，这也就是HWM的原始含义，当然不能说一个水库没水了，就说该水库的历史最高水位为0。

但是如果我们在表上使用了truncate命令，则该表的HWM会被重新置为0。

HWM数据库的操作有如下影响：

a）全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。

b）即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。

如何知道一个表的HWM？

a）首先对表进行分析:

ANALYZETABLEESTIMATE/COMPUTESTATISTICS;

b）SELECTblocks,empty_blocks,num_rows

FROMuser_tables

WHEREtable_name=;

BLOCKS列代表该表中曾经使用过得数据库块的数目，即水线。

EMPTY_BLOCKS代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。

让我们以一个有28672行的BIG_EMP1表为例进行说明：

1）SQL>SELECTsegment_name,segment_type,blocks

FROMdba_segments

WHEREsegment_name='BIG_EMP1';

SEGMENT_NAMESEGMENT_TYPEBLOCKSEXTENTS

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

BIG_EMP1TABLE10242

1rowselected.

2）SQL>ANALYZETABLEbig_emp1ESTIMATESTATISTICS;

Statementprocessed.

3）SQL>SELECTtable_name,num_rows,blocks,empty_blocks

FROMuser_tables

WHEREtable_name='BIG_EMP1';

TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKS

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

BIG_EMP128672700323

1rowselected.

注意：

BLOCKS+EMPTY_BLOCKS（700+323=1023）比DBA_SEGMENTS.BLOCKS少个数据库块，这是因为有一个数据库块被保留用作segmentheader。

DBA_SEGMENTS.BLOCKS表示分配给这个表的所有的数据库块的数目。

USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。

4）SQL>SELECTCOUNT（DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER（rowid）||

DBMS_ROWID.ROWID_RELATIVE_FNO（rowid））"Used"

FROMbig_emp1;

Used

----------

700

1rowselected.

5）SQL>DELETEfrombig_emp1;

28672rowsprocessed.

6）SQL>commit;

Statementprocessed.

7）SQL>ANALYZETABLEbig_emp1ESTIMATESTATISTICS;

Statementprocessed.

8）SQL>SELECTtable_name,num_rows,blocks,empty_blocks

FROMuser_tables

WHEREtable_name='BIG_EMP1';

TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKS

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

BIG_EMP10700323

1rowselected.

9）SQL>SELECTCOUNT（DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER（rowid）||

DBMS_ROWID.ROWID_RELATIVE_FNO（rowid））"Used"

FROMbig_emp1;

Used

----------

0--这表名没有任何数据库块容纳数据，即表中无数据

1rowselected.

10）SQL>TRUNCATETABLEbig_emp1;

Statementprocessed.

11）SQL>ANALYZETABLEbig_emp1ESTIMATESTATISTICS;

Statementprocessed.

12）SQL>SELECTtable_name,num_rows,blocks,empty_blocks

2>FROMuser_tables

3>WHEREtable_name='BIG_EMP1';

TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKS

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

BIG_EMP100511

1rowselected.

13）SQL>SELECTsegment_name,segment_type,blocks

FROMdba_segments

WHEREsegment_name='BIG_EMP1';

SEGMENT_NAMESEGMENT_TYPEBLOCKSEXTENTS

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

BIG_EMP1TABLE5121

1rowselected.

注意:

TRUNCATE命令回收了由delete命令产生的空闲空间，注意该表分配的空间由原先的1024块降为512块。

为了保留由delete命令产生的空闲空间，可以使用

TRUNCATETABLEbig_emp1REUSESTORAGE

用此命令后，该表还会是原先的1024块。

************************************************************

Oracle表段中的高水位线HWM  

在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。

水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线（High-wartermark,HWM）。

在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HWM为最低值。

当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。

也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。

下面我们来谈一下Oracle中Select语句的特性。

Select语句会对表中的数据进行一次扫描，但是究竟扫描多少数据存储块呢，这个并不是说数据库中有多少数据，Oracle就扫描这么大的数据块，而是Oracle会扫描高水位线以下的数据块。

现在来想象一下，如果刚才是一张刚刚建立的空表，你进行了一次Select操作，那么由于高水位线HWM在最低的0位置上，所以没有数据块需要被扫描，扫描时间会极短。

而如果这个时候你首先插入了一千万条数据，然后再用delete语句删除这一千万条数据。

由于插入了一千万条数据，所以这个时候的高水位线就在一千万条数据这里。

后来删除这一千万条数据的时候，由于delete语句不影响高水位线，所以高水位线依然在一千万条数据这里。

这个时候再一次用select语句进行扫描，虽然这个时候表中没有数据，但是由于扫描是按照高水位线来的，所以需要把一千万条数据的存储空间都要扫描一次，也就是说这次扫描所需要的时间和扫描一千万条数据所需要的时间是一样多的。

所以有时候有人总是经常说，怎么我的表中没有几条数据，但是还是这么慢呢，这个时候其实奥秘就是这里的高水位线了。

   那有没有办法让高水位线下降呢，其实有一种比较简单的方法，那就是采用TRUNCATE语句进行删除数据。

采用TRUNCATE语句删除一个表的数据的时候，类似于重新建立了表，不仅把数据都删除了，还把HWM给清空恢复为0。

所以如果需要把表清空，在有可能利用TRUNCATE语句来删除数据的时候就利用TRUNCATE语句来删除表，特别是那种数据量有可能很大的临时存储表。

   在手动段空间管理（ManualSegmentSpaceManagement）中，段中只有一个HWM，但是在Oracle9iRelease1才添加的自动段空间管理（AutomaticSegmentSpaceManagement）中，又有了一个低HWM的概念出来。

为什么有了HWM还又有一个低HWM呢，这个是因为自动段空间管理的特性造成的。

在手段段空间管理中，当数据插入以后，如果是插入到新的数据块中，数据块就会被自动格式化等待数据访问。

而在自动段空间管理中，数据插入到新的数据块以后，数据块并没有被格式化，而是在第一次在第一次访问这个数据块的时候才格式化这个块。

所以我们又需要一条水位线，用来标示已经被格式化的块。

这条水位线就叫做低HWM。

一般来说，低HWM肯定是低于等于HWM的。

************************************************

修正ORACLE表的高水位线

在ORACLE中，执行对表的删除操作不会降低该表的高水位线。

而全表扫描将始终读取一个段（extent）中所有低于高水位线标记的块。

如果在执行删除操作后不降低高水位线标记，则将导致查询语句的性能低下。

下面的方法都可以降低高水位线标记。

1.执行表重建指令altertabletable_namemove;

（在线转移表空间ALTERTABLE。

。

。

MOVETABLESPACE。

。

。

 ALTERTABLE。

。

。

MOVE后面不跟参数也行，

 不跟参数表还是在原来的表空间，move后记住重建索引

 如果以后还要继续向这个表增加数据，没有必要move，

 只是释放出来的空间，只能这个表用，其他的表或者segment无法使用该空间

 ）

2.执行altertabletable_nameshrinkspace;注意，此命令为Oracle10g新增功能，再执行该指令之前必须允许行移动altertabletable_nameenablerowmovement;

3.复制要保留的数据到临时表t，drop原表，然后rename临时表t为原表

4.emp/imp

5.alter table table_name deallocate unused  

6.尽量truncate吧

OracleSQL优化（转）

关键字:

oracle

OracleSQL的优化规则：

尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替

       用IN写出来的SQL的优点是比较容易写及清晰易懂，但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

      ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。

由此可见用IN的SQL至少多了一个转换的过程。

一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

       Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。

而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。

NOTEXISTS比NOTIN效率稍高。

但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。

       推荐方案：

在业务密集的SQL当中尽量不采用IN操作符。

不用NOTIN操作符，可以用NOTEXISTS或者外连接+替代

       此操作是强列推荐不使用的，因为它不能应用表的索引。

       推荐方案：

用NOTEXISTS或（外连接+判断为空）方案代替

不用“<>”或者“!

=”操作符。

对不等于操作符的处理会造成全表扫描，可以用“<”or“>”代替

       不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

       推荐方案：

用其它相同功能的操作运算代替，如：

       1）a<>0改为a>0ora<0

       2）a<>’’改为a>’’

Where子句中出现ISNULL或者ISNOTNULL时，Oracle会停止使用索引而执行全表扫描。

可以考虑在设计表时，对索引列设置为NOTNULL。

这样就可以用其他操作来取代判断NULL的操作

       ISNULL或ISNOTNULL操作（判断字段是否为空）

       判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

       推荐方案：

       用其它相同功能的操作运算代替，如：

       1）aisnotnull改为a>0或a>’’等。

       2）不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。

      3）建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）

当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用

对于有连接的列“||”，最后一个连接列索引会无效。

尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。

如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。

Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。

对数据类型不同的列进行比较时，会使索引失效。

>及<操作符（大于或小于操作符）

      大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。

那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

      推荐方案：

用“>=”替代“>”。

UNION操作符

      UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

实际大部分应用中是不会产生重复的记录，最常见的是过程表