Index partitioning

From: Pap <oracle.developer35_at_gmail.com>
Date: Wed, 14 Feb 2024 01:27:43 +0530
Message-ID: <CAEjw_fhFRYw=hD-Qgwuhg=su55wZ0cdu24RbgXmQU8+p+H11zQ_at_mail.gmail.com>



Hello, It's version 19C of oracle.

We are seeing high "buffer busy waits" and a certain point in time in the day and the object ASH/AWR , pointing to is an index block. That index is currently a local index in a weekly range partitioned table. The suggestion from a few team members is to make the index hash partitioned.

So my question is , can we hash partition the local index further without making the base table being hash subpartitioned on that column. Any sample syntax to do the same?

Asking this because, we normally don't have global indexes in any of the partitioned tables, considering the maintenance issues while dropping/purging historical partitions, as that will make the global index invalid. So all the existing indexes in the table are local indexes only. However making the base table range-hash partitioned will be a bigger change and will make all the local indexes being range-hash partitioned. So can we independently make the one specific local index(which is the point of contention) sub-partitioned by hash without changing the existing partitioning strategy of the base table?

Regards
Pap

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 13 2024 - 20:57:43 CET

Original text of this message