Re: Question on global index maintenance

Thank You So much Andy.

When you said "*And worthy of note (though kind of obvious), if the clean jobs aren't able to complete then those segments are continuously growing*.", Do you mean that if the delayed index maintenance/cleanup job is not getting completed but still we keep on dropping partitions in subsequent days. This can cause the global index to grow substantially and then it can impact the performance of the queries those are using this index access path in them?

As you mentioned , there are cases in which this index cleanup job can block the other sessions , which looks scary on a 24/7 system. And even for unique indexes we are being asked/forced to have the partition key included in the index keys so as to make it unique and local. So ,overall It means we are no better even on the higher/recent versions of Oracle for considering creation of global indexes on partitioned tables. Correct me if I'm wrong.

On Sat, Jan 8, 2022 at 2:38 AM Andy Klock <andy_at_klockmail.com> wrote:

>
> Hi Lok,
>
> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
> On Friday, January 7th, 2022 at 3:35 PM, Lok P <loknath.73_at_gmail.com>
> 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
>

--
http://www.freelists.org/webpage/oracle-l