A question came up on OTN a little while ago about an oddity during an online index rebuild. The original question describes how a relatively small index (500MB) takes several minutes to rebuild, with a few minutes where users who are trying to update the table seem to be stuck on enqueue wait events, waiting for locks on the table to be released. But Oracle tells us that “online” rebuilds of indexes don’t cause long-lasting locks to appear – is this a bug, or a new myth ?
My response to the question is speculative but, I hope, a reasonable suggestion that fits the original description. The amount of time that the locks are held at either end of the rebuild are dependent on the level (and timing) of activity on the table.
In Oracle 10g, I have managed to cause long waits for other users – even when the process doing the index rebuild took fractions of a second to get its final lock – by generating a very large volume of change as the initial stage of the index rebuild was taking place.
Note: this was not the same problem as the process doing the rebuild having to wait for the final lock because another user had uncommitted transactions on the table.