|Subject||Re: Question about ALTER INDEX|
> - Is there a way to "freeze" the index ? I mean that the index with aHow would this work? Once the index is no longer maintained, it would
> current state is available for select statements, but new inserts are
> done without altering the index.
become unreliable for use in selects. For example:
Assume an ascending index on Employee.Name
Lets assume now that you stop maintaining the index at this point, and
we add a new record then commit.
And then we want to run a select,
where Name Starting With 'M'
If the select statement relied on the index, then it would not know
about this new record. If it did a natural scan it would find it. An
index is only reliable if using it returns the same result that a
natural scan would return.
I am guessing you are doing a batch insert, and want to do so without
maintaining the index, then rebuild the index once complete. The
normal procedure may be to deactivate the index, but you are worried
about the cost of running day-to-day queries without that index.
It may be technically possible to come up with a method of doing this
in some future version (not with anything currently released) by
flagging an index as "update when committing". This obviously is not
going to work for PK, FK, constraint or unique indices which will be
need to be maintained so inconsistent data can not be inserted by
other transactions, and your transaction would have to ignore the
index, but given that most of the indices are probably PK, FK or
Unique constraint related, this probably won't be that useful anyway.