ITPub博客

首页 > Linux操作系统 > Linux操作系统 > alter index rebuild和rebuild online的区别

alter index rebuild和rebuild online的区别

原创 Linux操作系统 作者:tolywang 时间:2007-04-17 00:00:00 0 删除 编辑

SQL> explain plan for
2 alter index test.IND_OBJECT_ID rebuild;

Explained.

SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 6235 | 18705 | 10 |
| 1 | INDEX BUILD NON UNIQUE| IND_OBJECT_ID | | | |
| 2 | SORT CREATE INDEX | | 6235 | 18705 | |
| 3 | INDEX FAST FULL SCAN| IND_OBJECT_ID | 6235 | 18705 | |
-------------------------------------------------------------------------

Note: cpu costing is off

11 rows selected.

SQL> explain plan for
2 alter index test.IND_OBJECT_ID rebuild online;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 6235 | 18705 | 10 |
| 1 | INDEX BUILD NON UNIQUE| IND_OBJECT_ID | | | |
| 2 | SORT CREATE INDEX | | 6235 | 18705 | |
| 3 | TABLE ACCESS FULL | T | 6235 | 18705 | 10 |
-------------------------------------------------------------------------

Note: cpu costing is off

11 rows selected.

SQL>

可见rebuild 和rebuild online的扫描方式不同,但都会发生sort.

在rebuild online 时会报错

ORA-08120: Need to create SYS.IND_ONLINE$ table in order to (re)build index
Cause: Alter index Build/Rebuild online require existing of SYS.IND_ONLINE$ table.
Action: User/DBA needs to create sys.ind_online$ before alter the index /rdbms/admin/catcio.sql contains script to create ind_online$.

解决方法就是运行catcio.sql 创建SYS.IND_ONLINE$表

SQL> conn / as sysdba
Connected.
SQL> @D:oracleora92rdbmsadmincatcio

除了扫描方式不同外,rebuild 会阻塞dml语句而rebuild online则不会。

rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表,所有rebuild online时索引的变化都记录在这个表中,当新的索引创建完成后,把这个表的记录维护到新的索引中去,然后drop掉旧的索引,rebuild online就完成了。

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1479533

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84588/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 基金词典
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13382664