Hello Experts,
We have seen cases(in our 11.2.0.4 version databases) in which we really
need global indexes as because partition keys cant be used in certain
business scenarios. However because of the maintenance issues associated
with those like drop partition will make those global index unusable and
thus they have to be rebuild and for that time those indexes will be
inaccessible and in a 24/7 running system its problematic. And even we used
'UPDATE INDEXES' clause during the partition drop and we saw its still
causing index to be in UNUSABLE status for sometime and also it runs for
longer.
All these issues made us always believe and forced to go for local indexes
for almost all of the partition tables, even we find that less efficient in
many oltp scenarios. And also asking to create a global index always put
question on us to how we are going to do maintenance in this 24/7 system.
In 12.1 , we see there exists an option of 'asynchronous global index
maintenance' in which its say the "UPDATE INDEXES" clause now wont cause
immediate index maintenance to happen rather it will be deferred. and later
it will be taken care by the auto cleanup job or it can be done by manually
invoking DBMS_PART.cleanup_gidx.
So my question was , how feasible its is in reality , if its reliable one
and now we can think of creating global indexes without hesitation now on
12.1 onward where-ever needed?
Is this 12.1 version, deferred global index maintenance going to take same
time as it used to happen during 11.2 version with immediate 'update
indexes' option or its faster? Or any other downside, say if during this
auto deferred maintenance it will block any DML/SELECT statements etc?
