Rebuild index VS. rebuild index online

When rebuild index VS. rebuild index online create index and rebuild index are used, the No. 4 lock will be applied to the original table, and the No. 3 lock will be applied to obj $ to prevent DML operations on the original table. Create index online and rebuild index online use a new temporary table to modify the index column of the original table when creating or recreating the index. In this way, you do not need to lock the table, make sure that the dml operation of the original table can be executed normally. During the rebuild process, you can still use the old index for query. Re-indexing the index requires twice the original index space. Test 1: create Table locks conn scott/tigerselect sid, username from v $ session; create index test_index on test (name); sid username ------- ----------- 139 143 scott SQL> SELECT OBJECT_NAME, lmode from v $ lock l, DBA_OBJECTS o where o. OBJECT_ID = L. ID1 and l. TYPE = 'Tm 'and sid = 143; OBJECT_NAME LMODE ------------------------------ ---------- OBJ $3 TEST 4 TEST 2: rebuild online lock alter index test_index rebuild online; SELE CT OBJECT_NAME, lmode from v $ lock l, DBA_OBJECTS o where o. OBJECT_ID = L. ID1 and l. TYPE = 'Tm 'and sid = 143; OBJECT_NAME LMODE ------------------------------ SYS_JOURNAL_10499 4 TEST 2 lock TYPE 0, 'none', 1, 'null', 2, 'row-S (SS) ', 3, 'row-X (SX)', 4, 'share ', 5,'s/Row-X (SSX )', 6. 'clusive ', Test 3: How the database is recorded during online reconstruction. First, create a large table to ensure that you have enough time to observe the details of the index reconstruction process. SQL> create table test_rebuild as select * from dba_objects; Table created. SQL> insert into test_rebuild select * from test_rebuild; 6398 rows created. SQL>/409472 rows created. SQL> commit; Commit complete. SQL> desc test_rebuild Name Null? Type reserved -------- invalid OWNER VARCHAR2 (30) OBJECT_NAME VARCHAR2 (128) SUBOBJECT_NAME VARCHAR2 (30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2 (18) created date updated TIMESTAMP VARCHAR2 (19) STATUS VARCHAR2 (7) TEMPORARY VARCHAR2 (1) GENERATED VARCHAR2 (1) SECONDARY VARCHAR2 (1) SQL> create index idx_rebuild on test_rebuild (Object_name); Index created. next, let's start with three session15: 13: 09 session1> alter index idx_rebuild rebuild online; 15:13:30 session2> select rowid, object_name from scott. test_rebuild where rownum <2; ROWID OBJECT_NAME ------------------ --------------------------------------------------------- aaablaafaaaai0aaa old 15:13:41 session2> update scott. test_rebuild set object_name = 'new' where rownum <2; 1 row updated. 15:13:54 SQL> commit; Commit complete. 15:13:57 session3> select * from scott. SYS_JOURNAL_6668; C0 o ptode partno rid ------------------- --------------------------------- old D 0 AAAAABAAFAAAAI0AAAnew I 0 AAAAABAAFAAAAI0AAA when session1 reports Index altered. then query select * from scott. SYS_JOURNAL_6668; the system will prompt that the table does not exist. When oracle is modifying to the index column, it will write the modified value to a newly created table SYS_JOURNAL_xxxx. xxxx indicates the object_id of the index, you can query select index_name, object _ Id from user_objects. For example, in the update operation, two actions are recorded in the temporary creation table. An old value D indicates deletion, a new value I indicates insertion, and a modified rowid is recorded. SYS_JOURNAL_xxxx does not have any records when it is modified to a non-index column. Supplement: If the composite index create index idx_rebuild on test_rebuild (object_name, status) online is created, select * from scott is queried. SYS_JOURNAL_6668; C0 C1 o partno rid certificate ------------------ ------------------ new invalid I 0 baeaaafaaaai0aaaold invalid D 0 baeaaafaaaai0aaa created a combined index of two columns, the temporary creation table contains two columns starting with C.