Re: Question on global index maintenance

This whole area is one that has to be tested at scale (and old and new versions of Oracle may behave differently), with some concurrency; and you may find that non-unique and unique indexes behave differently. And there are 4 different ways to do the cleanup, so there's a potential for a lot of testing.

I was suprised by Andy's comment about locking problems - though if you have a unique index with a row in one partition and drop the partition and then start a cleanup, an insert of the same unique key into another partition may see some odd locking problems (which may depend on whether the cleanup gets to that value first or the new insert gets there first) as the insert will want to re-use the index entry and update the rowid "carried data" component. This is the sort of thing that doesn't get described in the manuals and can be very hard to determine in small tests; and it's the sort of thing that customers discover and report to Oracle and they fix in the next release and create patches for.

The biggest problem for DBAs is that there are some things that you'll only see as problems when you're operating at a scale that's larger than Oracle's internal tests and do something is different from Oracle's test cases.

Regards
Jonathan Lewis

On Sat, 8 Jan 2022 at 17:30, Lok P <loknath.73_at_gmail.com> wrote:

> Thank you very much Jonathan and Andy.
>
> Thanks for correcting me. So basically in case of a unique constraint ,
> the index has to be global index or global partitioned index but it can't
> be a local index because that will be then unique for each day/partition
> but won't be at the table lebel. So considering that fact , we were
> lucky in our case as in most scenarios we were good with the local unique
> index which is not exactly unique at the table level.
>
> But it's true that we can't avoid the global index for a scenario in which
> the partition key(say date column) does not necessarily have to be part of
> the uniqueness. And in those cases we have to come across the index
> maintenance overhead stuff while dropping/truncating/purging data from the
> partition table.
>
> So is there any way to get this work on a 24/7 system? Say as we move
> ahead, we want to drop/purge one daily range partition from the history
> each day and in that case the global index(or say primary key index) has to
> be rebuilt after the drop partition immediately(but that can cause issue if
> it get unusable+rebuild in a 24/7 system) or has to pass through a delayed
> maintenance phase through 'update indexes' syntax. So in this case even as
> Andy mentioned there are chances of blocking while the maintenance is
> happening, so is there a real online way of achieving this global index
> requirement without making it unusable for big partitioned tables?
> Or it has to be then partitioned (say e.g hash partitioned through the
> same PK column and then it would not block others sessions during the
> delayed cleanup?
>
>
>
> On Sat, Jan 8, 2022 at 3:59 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Forget about adding the partition key to indexes that are currently
>> unique in order to make them local.
>>
>> The uniqueness requirement is about the CONSTRAINT, not the index. If
>> you add the partition key to the unique constraing the original unique
>> condition is no longer unique so you could get duplicates spread across
>> different partitions.
>>
>> e.g. if order_id is considered to be unique but order_date is the
>> partition key, then
>> a) the index on (order_id) can be unique, but has to be global (or
>> globally partitioned).
>> or
>> b) the index on (order_id, order_date) can be unique and local, but only
>> if you now allow duplicates of order_id.
>> or
>> c) the index on (order_id, order_data) can be non-unique and local, but
>> if you create a unique constraint on order_id Oracle will create a global
>> unique index on (order_id)
>>
>>
>> In this example (where you are not likely to execute a query "where
>> order_id between X and Y") a damage-limiting exercise is to create the
>> index as globally hash partitioned so that "order_id = X" requires only one
>> index partition to be visitied and the index partitions are reduced in size
>> hence easier to handle when doing house-keeping. (e.g. when rebuilding).
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>> On Sat, 8 Jan 2022 at 06:52, Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> 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