Rebuild index mechanism

Alter index... rebuild online mechanism

When we rebuild an index without the online option, Oracle directly reads the data of the original index. When we add the online option, Oracle directly scans the data in the table, so how to maintain the consistency of the index segment data? It is the data change from the start of creation to the completion of index creation...

From the moment the index starts to rebuild online, Oracle will first create a system temporary log table sys_journal_xxx. The structure is similar to the mlog $ _ table, through the internal trigger, records the changes in the table when the rebuild index starts. After the index is created, new data is directly written into the index, you only need to maintain the changes in the sys_journal_xxx log table to the index.

1. Drop & create, the current table cannot use this index, it may seriously affect the application, it can only be used when the application does not need this index, and alter index .. rebuild online does not have this restriction, but it only consumes dB resources and does not seriously affect applications.

2. Drop & create only occupies the space of one index, unlike alter index .. rebuild online will occupy the space of the New and Old indexes during the creation process. In environments with insufficient free space, you may only use drop & create

3. As to whether alter index... rebuild, or alter index... rebuild online uses index scan instead of table scan, it seems that different environments have different results. (In RBO status)
In my environment, alter index .. rebuild => index fast full scan
Alter index .. rebuild online ==> table full scan
The other is that alter index... rebuild/alter index... rebuild online uses table full scan.