oracle表碎片起因和解决办法.docx

Oracle表碎片起因及解决办法 2009-05-14 15:42 今天发现在一个SQL查询用不到索引，classic!是建了索引的，如下： select * from infobase where classic! in( 10001,10002,10003,10004,10005); 奇怪的发现在classid in( 10001,10002)的值两以上就用不索引，两个以下就可以用 到，开始怀疑是索弓I有问题，于是就重建下了下classid ±的索弓|还是不行。从 网上找到一篇文章才知道可能是表中存在碎片的问题 于是用下面的步骤解决： 1、 重建表： create table infobase2 select * from infobase; 2、 改以前的表名： alter table infobase rename to infobase3; 3、 改新建表名为以前表名： alter table infobase2 rename to infobase; 4、 建上索引： create index classid_ind on infobase(classid); 可是过了一天问题又出现了，索引又是不能使用了，然后执行下面的语句解决： ANALYZE TABLE INFOBASE compute Statistics; 或是 ANALYZE TABLE INFOBASE estimate STATISTICS SAMPLE 50 PERCENT ; --注意：50 PERCENT值太小索引可能还是不起作用，我就开始用20 PERCENT 时，索引还是用不上。 跟表碎片有关的基础知识： 什么是水线(High Water Mark)? 所有的oracle段(segments,在此，为了理解方便，建议把segment作为表的一 个同义词)都有一个在段内容纳数据的上限,我们把这个上限称为nhigh water mark”或HWMO这个HWM是一个标记，用来说明已经有多少没有使用的数据 块分配给这个segmento HWM通常增长的幅度为一次5个数据块，原则上HWM 只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这 个特点，使HWM很象一个水库的历史最高水位，这也就是HWM的原始含义， 当然不能说一个水库没水了，就说该水库的历史最高水位为0。但是如果我们在 表上使用了 truncate命令，则该表的HWM会被重新置为0。 HWM数据库的操作有如下影响： 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表 中没有任何数据。 即使HWM以下有空闲的数据库块，键入在插入数据时使用了 append关键字, 则在插入时使用HWM以上的数据块，此肘HWM会自动增大。 如何知道一个表的HWM? 首先对表进行分析： ANALYZE TABLE tablename ESTIMATE/COMPUTE STATISTICS; SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = tablename; BLOCKS列代表该表中曾经使用过得数据库块的数目，即水线。 EMPTY_BLOCKS代表分配给该表，但是在水线以上的数据库块，即从来没有 使用的数据块。 让我们以一个有28672行的BIG.EMP1表为例进行说明： SQL SELECT segment_nanie,segment_type,blocks FROM dba_segments WHERE segment_name=BIG_EMP 1 SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS BIG.EMP1 TABLE 1024 2 1 row selected. SQL ANALYZE TABLE big_empl ESTIMATE STATISTICS; Statement processed. SQL SELECT table_nanie,num_rows,blocks,en】pty_blocks FROM user^tables WHERE table namc=BIG EMP1; TABLE_NAME NUM.ROWS BLOCKS EMPTY_BLOCKS BIG_EMP1 28672 700 323 1 row selected. 注意： BLOCKS + EMPTY_BLOCKS (700+323=1023)比 DBA_SEGMENTS.BLOCKS 少 个数据库块，这是因为有一个数据库块被保留用作segment headero DBA_SEGMENTS.BLOCKS表示分配给这个表的所有的数据库块的数目。 USER