oracle10g_alter index rebuild_online_重構索引

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.