alter index idx_t_hwm_a rebuild;

--持锁情况
sysdba_session>/

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
       193 AE        100          0          4          0
       193 OD     135021          0          6          0
       193 DL     135014          0          3          0
       193 DL     135014          0          3          0
       193 TM     135014          0          4          0
       193 TS          4   17101802          6          0
       193 TX     131102      23099          6          0

已选择7行。

sysdba_session>/

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
193 AE 100 0 4 0

---------
alter index idx_t_hwm_a rebuild online;
sysdba_session>/

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
       193 AE        100          0          4          0
       193 OD     135021          0          6          0
       193 DL     135014          0          3          0
       193 DL     135014          0          3          0
       193 OD     135014          0          4          0
       193 TM     135014          0          2          0
       193 TM     135024          0          4          0
       193 TX     589854      36264          6          0

已选择8行。

sysdba_session>

小结:重建索引时,有无选项online持锁不同

---一边运行如下语句
SQL> alter index idx_t_hwm_a rebuild online;

--在另一会话运行dml,查看是否阻塞dml
insert_session>insert into t_hwm select 1,2 from dual;

已创建 1 行。

insert_session>commit;

提交完成。

insert_session>update t_hwm set a=22 where rownum=1;

已更新 1 行。

insert_session>commit;

提交完成。

insert_session>delete from t_hwm where rownum<=2;

已删除2行。

insert_session>commit;

提交完成。

-------小结:
1,alter index rebuild online不会阻塞dml操作
2,而alter index rebuild;会阻塞dml操作,如果要重构的索引很大,阻塞的时间会很长.

补充:

Restrictions on Online Indexes Online indexes are subject to the following restrictions:

Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle Database returns an error.
译:在线重构索引期间,并行dml不被支持(并行这个东东有空要细研一番)

You cannot specify ONLINE for a bitmap join index or a cluster index.
译:位图连接索引或cluster index不适用于在线重构索引

For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that
are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

ITPub博客

oracle10g_alter index rebuild_online_重构索引

最新文章