Question :
I have a very large table (1.9 million records) that currently has a unique index. I need to change that unique index to a non-unique index.
Because I am trying to avoid an “outage” from this activity, I am considering taking this clever approach to creating the new non-unique index. Creating the index in this way means that a working index is always available to benefit queries of that table–this benefit solves one of the arguments for an outage.
What is not clear to me is what adverse affects I am risking by building this index without restricting updates/inserts on the table. If those risks are negligible, that would remove the remaining need for an outage.
What complications arise from edits to a table receiving a new index?
Answer :
Standard index build locks the table for the whole index build process, and DML statements on the table will be blocked. You may call that a complication.
Online index build is a feature of Enterprise Edition.
This method locks the table at the beginning, and at the end of the index build process. In between, DML operations on the table can run as usual.