Re: Question on global index maintenance
- From: Andy Klock <andy@xxxxxxxxxxxxx>
- To: loknath.73@xxxxxxxxx
- Date: Fri, 07 Jan 2022 21:08:35 +0000
Hi Lok,
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, January 7th, 2022 at 3:35 PM, Lok P <loknath.73@xxxxxxxxx> wrote:
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?
Global indexes are still feasible and sometimes necessary (unique constraints,
for example), but because of the maintenance you speak of, local indexes would
be preferred where appropriate.
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?
Partition DROPS and TRUNCATES are measurably faster now that Oracle is
deferring the cleaning up of orphaned entries, but your concerns are still very
valid. Depending on size, I've had cases where cleaning wasn't able to complete
within the maintenance window, even with parallelism enabled. There were also
cases where sessions were being blocked by the clean job and those segments had
to be deferred and index rebuilds had to be done during downtime windows. And
worthy of note (though kind of obvious), if the clean jobs aren't able to
complete then those segments are continuously growing.
Andy K
Other related posts:
