Oracle alter index rebuild 与 ORA-08104 说明

时间：2022-03-14 00:28

一. 官网说明

在MOS 上的一篇文章讲到了rebuild online 和offline的区别：

Index Rebuild Is Hanging Or Taking Too Long [ID 272762.1]

Symptoms:
=========
       Performance issues while rebuilding very large indexes. The offline rebuilds of their index is relatively quick -finishes in 15 minutes. Issuing index rebuild ONLINE statement => finishes in about an hour. This behavior of ONLINE index rebuilds makes it a non-option for large tables as it just takes too long to scan the table to rebuild the index. The offline may not be feasible due to due to the 24/7 nature of the database. This may be a loss of functionality for such situations. If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter hanging behavior indefinitely (or more than 6 hours).

DIAGNOSTIC ANALYSIS:
--------------------
       We can trace the sessions rebuilding the indexes with 10046 level 12. Comparing the IO reads for the index-rebuild and the index-rebuild-online reveals the following:
       ONLINE index rebuilds： It scans the base table and it doesn‘t scan the blocks of the index.
       OFFLINE index rebuilds：It scans the index for the build operation.

This behaviour is across all versions.

Cause/Explanation
=============
When you rebuild index online, it will do a full table scan on the base table. At the same time it will maintain a journal table for DML data, which has changed during this index rebuilding operation. So it should take longer time, specially if you do lots of DML on the same table,while rebuilding index online.
-- rebuild index online的时候，会选择全表扫描，同时会维护一个中间日志表，用来记录在rebuild 期间的增量数据，原理类似于物化视图日志，日志表是一个索引组织表(IOT)，这张中间表只有插入，不会有删除和修改操作，而且只有主键条件查询，正是IOT最合适的场景。

On the other hand, while rebuilding the index without online option, Oracle will grab the index in X-mode and rebuild a new index segment by selecting the data from the old index. So here we are not allowing any DML on the table hence there is no journal table involved and it is doing an index scan. Hence it will be pretty fast.

--rebuild offline时，选择的6模式的X 锁，它根据old index 来rebuild。因此不允许进行DML，也就没有中间表。因此也比较块。

Solution/Conclusion:
===========
- The ONLINE index rebuild reads the base table, and this is by design.
- Rebuilding index ONLINE is pretty slow.
- Rebuilding index offline is very fast, but it prevents any DML on the base table.

二. rebuild index 说明

有关锁的模式信息如下：


Parameter	Description
object_id	Object id of index to be cleaned up. The default cleans up all object ids that qualify.
wait_for_lock	This parameter specifies whether to try getting DML locks on underlying table [[sub]partition] object. The default retries up to an internal retry limit, after which the lock get will give up. If LOCK_NOWAIT is specified, then the lock get does not retry.