oracle重建索引

重建索引有多种方式,如drop and re-create、rebuild、rebuild online等。
下面简单比较这几种方式异同以及优缺点:
  首先建立测试表及数据:
  SQL> CREATE TABLE TEST AS SELECT CITYCODE C1 FROM CITIZENINFO2;
  Table created
  SQL> ALTER TABLE TEST MODIFY C1 NOT NULL;
  Table altered
  SQL> SELECT COUNT(1) FROM TEST;
  COUNT(1)
  ----------
  16000000
  一、drop and re-create和rebuild
  首先看看正常建立索引时,对表的加锁情况。
  suk@ORACLE9I> @show_sid
  SID
  ----------
  14
  suk@ORACLE9I> CREATE INDEX IDX_TEST_C1 ON TEST(C1);
  索引已创建。
  SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
  OBJECT_NAME LMODE
  ------------------------------ ----------
  OBJ$ 3
  TEST 4
  可见,普通情况下建立索引时, oracle会对基表加share锁,由于share锁和 row-X是不兼容的,也就是说,在建立索引期间,无法对基表进行DML操作。
  对于删除重建索引的方法就不介绍了,它与上面的描述是一样的,下面我们看看用rebuild的方式建立索引有什么特别。
  suk@ORACLE9I> ALTER INDEX IDX_TEST_C1 REBUILD;
  索引已更改。
  另开一个会话,查询此时test的加锁情况:
  SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
  OBJECT_NAME LMODE
  ------------------------------ ----------
  TEST 4
  可见,rebuild的方式对基表的加锁方式与CREATE时是一样的。
  另开一个会话,在索引正在rebuild时,执行如下 SQL
  suk@ORACLE9I> SET AUTOTRACE TRACE
  suk@ORACLE9I> SELECT COUNT(1) FROM TEST WHERE ROWNUM<10;
  执行计划
  ----------------------------------------------------------
  0 SELECT STATEMENT ptimizer=CHOOSE (Cost=26 Card=1)
  1 0 SORT (AGGREGATE)
  2 1 COUNT (STOPKEY)
  3 2 INDEX (FULL SCAN) OF 'IDX_TEST_C1' (NON-UNIQUE) (Cost=
  26 Card=1986621)
  可以看到索引在重建时,查询仍然可以使用旧索引。实际上,oracle在rebuild时,在创建新索引过程中,并不会删除旧索引,直到新索引rebuild成功。
  从这点可以知道rebuild比删除重建的一个好处是不会影响原有的SQL查询,但也正由于此,用rebuild方式建立索引需要相应表空间的空闲空间是删除重建方式的2倍。
重建索引有多种方式,如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点:
  相关文章:
  oracle重建索引(一)
  二、rebuild 和rebuild online
  首先我们跟踪一下rebuild online的过程。
  另开一个会话查看锁的信息:
  SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
  OBJECT_NAME LMODE
  ------------------------------ ----------
  SYS_JOURNAL_10499 4
  TEST 2
  SQL> INSERT INTO TEST VALUES(11);
  1 row inserted
  SQL> COMMIT;
  Commit complete
  可以看到,在rebuild online期间,oracle对基表加的是RS锁,此时我们可以对基表进行DML操作。但奇怪的话在相同的session中有一个SYS_JOURNAL_10499表被加SHARE锁,这个表是干什么用的呢?
  我们看看trace文件,有这样的信息:
  create table "SUK"."SYS_JOURNAL_10499" (C0 NUMBER(6,0), opcode char(1),
  partno number, rid rowid, primary key( C0 , rid )) organization index
  TABLESPACE "TEST"
  CREATE UNIQUE INDEX "SUK"."SYS_IOT_TOP_10605" on
  "SUK"."SYS_JOURNAL_10499"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "TEST"
  NOPARALLEL
  drop table "SUK"."SYS_JOURNAL_10499"
  我们在查查10499是什么东西:
  SQL> SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=10499;
  OBJECT_NAME OBJECT_TYPE
  ------------------------------ ------------------
  IDX_TEST_C1 INDEX
  从这些信息可以推测:表SYS_JOURNAL_10499就是实现在重建索引时不阻塞DML操作而 设计的,它存储的是在索引重建期间发生在基表的数据变化。可以推测,CREATE INDEX .... ONLINE应该也有一张类似的表。
  实际上,oracle之所以在创建索引时锁表阻止DML操作就是为了防止不能索引新变化的数据,在online方式重建时,有了临时表SYS_JOURNAL_XXXX,oracle就可以放心大胆地让用户操作了,因为所有重建索引期间的数据变化信息都会保留在SYS_JOURNAL_XXX表中,当索引重建完后再加上SYS_JOURNAL_XXX记录的数据,就不会漏索引数据了。(XXX是被重建的索引对应的OBJECT_ID) 
        导读:
  重建索引有多种方式,如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点:
  相关文章:
  oracle重建索引(一)
  oracle重建索引(二)
  三、rebuild和rebuild online的数据源
  网上一直有这样一个说法:重建索引是以原索引作为数据源的。那么,这种说法是否准确呢?我们做 实验来验证一下:
  suk@ORACLE9I> COL SEGMENT_NAME FORMAT A30
  --首先看看表和索引的大小
  suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C1');
  SEGMENT_NAME BYTES
  ------------------------------ ----------
  TEST 201326592
  IDX_TEST_C1 293601280
  suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;
  已解释。
  suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  -----------------------------------------------------------------------
  | Id | Operation | Name | Rows | Bytes | Cost |
  -----------------------------------------------------------------------
  | 0 | ALTER INDEX STATEMENT | | | | |
  | 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
  | 2 | SORT CREATE INDEX | | | | |
  | 3 | TABLE ACCESS FULL | TEST | | | |
  -----------------------------------------------------------------------
  Note: rule based optimization
  已选择11行。
  --从执行计划可以看出,当索引比表大时,rebuild索引用的数据源是基表。
  suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;
  已解释。
  suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  -----------------------------------------------------------------------
  | Id | Operation | Name | Rows | Bytes | Cost |
  -----------------------------------------------------------------------
  | 0 | ALTER INDEX STATEMENT | | | | |
  | 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
  | 2 | SORT CREATE INDEX | | | | |
  | 3 | TABLE ACCESS FULL | TEST | | | |
  -----------------------------------------------------------------------
  Note: rule based optimization
  已选择11行。
  --从执行计划可以看出,当索引比表大时,rebuild online索引用的数据源是基表。
  --我们为TEST添加一列,使得表比索引大
  suk@ORACLE9I> ALTER TABLE TEST ADD(C2 CHAR(30) DEFAULT '1');
  表已更改。
  suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C
  1');
  SEGMENT_NAME BYTES
  ------------------------------ ----------
  TEST 1476395008
  IDX_TEST_C1 293601280
  suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;
  已解释。
  suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  -----------------------------------------------------------------------
  | Id | Operation | Name | Rows | Bytes | Cost |
  -----------------------------------------------------------------------
  | 0 | ALTER INDEX STATEMENT | | | | |
  | 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
  | 2 | SORT CREATE INDEX | | | | |
  | 3 | INDEX FAST FULL SCAN| IDX_TEST_C1 | | | |
  -----------------------------------------------------------------------
  Note: rule based optimization
  已选择11行。
  --从执行计划可以看出,当表比索引大时,执行计划已经改变,rebuild索引是以索引作为数据源的。
  suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;
  已解释。
  suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  -----------------------------------------------------------------------
  | Id | Operation | Name | Rows | Bytes | Cost |
  -----------------------------------------------------------------------
  | 0 | ALTER INDEX STATEMENT | | | | |
  | 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
  | 2 | SORT CREATE INDEX | | | | |
  | 3 | TABLE ACCESS FULL | TEST | | | |
  -----------------------------------------------------------------------
  Note: rule based optimization
  已选择11行。
  --从执行计划可以看出,当表比索引大时,rebuild online仍然以基表作为数据源。
  rebuild模式下,因为表数据不会产生变化,oracle主要考虑性能问题,把更快扫描完成的段作为数据源。在上面的例子中,我们并没有对表进行分析,故oracle应该根据数据段的大小来决定那个作为数据源的。一般索引字段比较多,或者对索引字段的DML操作较多,可能会导致索引比表大,这时oracle就会使用基表作为新索引的数据源进行rebuild了。
  而在rebuild online模式下,因为允许DML操作,而表数据变化的同时索引也会跟着变化,为了索引与基表数据的一致性,必须采用基表数据作为数据源,而不能用原索引数据作为数据源。
  我们用反证法证明不能用原索引作为新索引的数据源。
  例如:
  T1发出rebuild online命令
  T2删除某条数据,删数据的同时,oracle会自动维护了旧索引
  T3扫描经过T2数据所在索引节点
  T4插入一条记录,新记录对应的索引节点刚好重用了T2删除的数据对应的索引节点空间
  如果是这样的话,新建的索引将不包含T4插入的记录的信息。所以,rebuild online情况下新索引的数据源不能是原索引。
  rebuild online情况下,如果非用原索引作为新索引的数据源的话,用中间表记录索引变化的方法应该是可以实现的,但由于数据变化会同时引起索引变化的特定决定了这种方法将异常复杂及效率底下,所以oracle不考虑旧索引作为新索引的数据源是有道理的。
  结论:
  1、rebuild会阻塞对基表的DML操作,但不会影响rebuild期间查询对原有索引的使用。
  2、rebuild的数据源可能是基表,也可能是原索引。取决于基表和原索引的大小,哪个小,rebuild时就会用那个作为数据源。这也说明了网上盛传的rebuild以原索引作为 数据库的说法是不完全正确的。
  3、rebuild online运行用户在索引重建期间执行DML操作。
  4、rebuild online的数据源是基表。
一、重建索引的前提 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。 二、重建索引的标准 1、索引重建是否有必要,一般看索引是否倾斜的严重,是否浪费了空间, 那应该如何才可以判断索引是否倾斜的严重,是否浪费了空间, 对索引进行结构分析(如下): SQL>Analyze index index_name validate structure; 2、在执行步骤1的session中查询index_stats表,不要到别的session去查询。 SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats; 说明:当 查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 , 该索引考虑重建 。 举例: (t_gl_assistbalance 26 万多条信息 ) SQL> select count(*) from t_gl_assistbalance ; 输出结果: COUNT(*) ---------- 265788 SQL> Analyze index IX_GL_ASSTBAL_1 validate structure; Index analyzed SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; 输出结果: HEIGHT DEL_LF_ROWS/LF_ROWS ---------- ------------------- 4 1 三、重建索引的方式 1、drop 原来的索引,然后再创建索引; 举例: 删除索引:drop index IX_PM_USERGROUP; 创建索引:create index IX_PM_USERGROUP on T_PM_USER (fgroupid); 说明:此方式耗时间,无法在24*7环境中实现,不建议使用。 2 、直接重建: 举例: alter index indexname rebuild; 或alter index indexname rebuild online; 说明:此方式比较快,可以在24*7环境中实现,建议使用此方式。 四、alter index rebuild 内部过程和注意点 alter index rebuild 和alter index rebuil online的区别 1、扫描方式不同 1.1、Rebuild以index fast full scan(or table full scan) 方式读取原索引中的数据来构建一个新的索引,有排序的操作; 1.2、rebuild online 执行表扫描获取数据,有排序的操作; 说明:Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost) 举例1 SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| | --------------------------------------------------------------------- 举例2 SQL> explain plan for alter index idx_policy_id rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | | | 2 | SORT CREATE INDEX | | 2072K| 9M| | | 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 | 举例3 ( 注意和 举例1 比较 ) Rebuil online 方式 : SQL> explain plan for alter index idx_policy_id2 rebuild online; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 | 2 、rebuild 会阻塞 dml 操作 ,rebuild online 不会阻塞 dml 操作 ; 3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表 , 所有 rebuild online 时索引的变化都记录在这个表中 , 当新的索引创建完成后 , 把这个表的记录维护到新的索引中去 , 然后 drop 掉旧的索引rebuild online 就完成了。 注意点: 1、 执行rebuild操作时,需要检查表空间是否足够; 2、虽然说rebuild online操作允许dml操作,但是还是建议在业务不繁忙时间段进行; Rebuild操作会产生大量redo log ; 五、重建分区表上的分区索引 重建分区索引方法: Alter index indexname rebuild partition paritionname tablespace tablespacename; Alter index indexname rebuild subpartition partitioname tablespace tablespacename; Partition name 可以从user_ind_partitions查找 Tablepace 参数允许alter index操作更改索引的存储空间; 六、索引状态描述 在数据字典中查看索引状态,发现有三种: valid:当前索引有效 N/A :分区索引 有效 unusable:索引失效 七、术语 1、高基数:简单理解就是表中列的不同值多。 2、低基数:建单理解就是表中的列的不同值少。 3、以删除的叶节点数量:指得是数据行的delete操作从逻辑上删除的索引节点 的数量,要记住oracle在删除数据行后,将 “ 死 “ 节点保留在索引中,这样做可以加快sql删除操作的速度,因此oracle删除数据行后可以不必重新平衡索引。 4、索引高度:索引高度是指由于数据行的插入操作而产生的索引层数,当表中添加大量数据时,oracle将生成索引的新层次以适应加入的数据行,因此,oracle索引可能有4层,但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目,而具备4层或是更多层的需要重建。 5、每次索引访问的读取数:是指利用索引读取一数据行时所需要的逻辑I/O操作数,逻辑读取不必是物理读取,因为索引的许多内容已经保存在数据缓冲区,然而,任何数据大于10的索引都需要重建。 6、什么时候重建呢? 察看 dba_indexes 中的 blevel 。这列是说明索引从根块到叶快的级别,或是深度。如果级别大于等于4。则需要重建, 如下 :Select index_name,blevel from dba_indexes where blevel>=4. 另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时,也应当重建,如下 SQL>analyze index index_name validate structure SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’ 就能看到是否这个索引被删除的百分比。 7、什么样的重建方式更好? (1)、建索引的办法: 1.1、删除并从头开始建立索引。 1.2 、 使用 alter index index_name rebuild 命令重建索引。 1.3 、 使用 alter index index_name coalesce 命令重建索引。 (2)、下面讨论一下这三种方法的优缺点: 2.1、删除并从头开始建索引:方法是最慢的,最耗时的。一般不建议。 2.2、Alter index index_name rebuild 快速重建索引的一种有效的办法,因为使用现有索引项来重建索引,如果客户操作时有其他用户在对这个表操作,尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题,alter index index_name rebuild online。 但是,由于新旧索引在建立时同时存在,因此,使用这种技巧则需要有额外的磁盘空间可临时使用,当索引建完后把老索引删除,如果没有成功,也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。 Alter index index_name rebuild tablespace tablespace_name 。 这个命令的执行步骤如下: 首先,逐一读取现有索引,以获取索引的关键字。 其次,按新的结构填写临时数据段。 最后,一旦操作成功,删除原有索引树,降临时数据段重命名为新的索引。 需要注意的是alter index index_name rebuild 命令中必须使用tablespace字句,以保证重建工作是在现有索引相同的表空间进行。 2.3、alter index index_name coalesce 使用带有coalesce参数时重建期间不需要额外空间,它只是在重建索引时将处于同一个索引分支内的叶块拼合起来,这最大限度的减少了与查询过程中相关的潜在的加锁问题,但是,coalesce选项不能用来将一个索引转移到其他表空间。 八、其他 1、truncate 分区操作和truncate 普通表的区别? 1.1、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响; 1.2、Truncate 分区操作不会释放全局索引中的空间,而truncate 普通表会释放索引所占空间; 2、rename 表名操作对索引没有影响,因为rename操作只是更改了数据字典,表中数据行的rowid并没有发生变化 总结: 1、判断是否需要重建索引: SQL>analyze index index_name validate structure; SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; ( 或 Select index_name,blevel from dba_indexes where blevel>=4 ); 说明 : 当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 , 该索引考虑重建 ; 2 、重建索引方法 : 方法一、 Alter index index_name rebuild tablespace tablespace_name; 优点:是快速重建索引的一种有效的办法,可以用来将一个索引移到新的表空间。 缺点:重建期间需要额外空间。 方法二、 alter index index_name coalesce; 优点:重建期间不需要额外空间。 缺点:coalesce选项不能用来将一个索引转移到其他表空间。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值