Question on global index maintenance

From: Lok P <loknath.73_at_gmail.com>
Date: Sat, 8 Jan 2022 02:05:41 +0530
Message-ID: <CAKna9VbQhZPLr9GfZ_eCsj8EKzwA1YQH5udy=Ospoz3sOeCBrw_at_mail.gmail.com>



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?

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 07 2022 - 21:35:41 CET

Original text of this message