Re: Creating Nonpartitioned Indexe vs. Partitioned Global Prefixed on Nonpartitioned Table

Am I the original author, Fred? :-)

It's been too long for me to remember the details (table/column names), but I have a feeling this was me, and it was exactly for the reason Karth outlined above.

On Mon, Jan 18, 2021 at 5:18 PM Karthikeyan Panchanathan < keyantech_at_gmail.com> wrote:

> One of my references by Uwe Hesse
>
>
> https://uhesse.com/2016/12/06/how-to-reduce-buffer-busy-waits-with-hash-partitioned-tables-in-oracle/
>
>
>
> Get Outlook for iOS <https://aka.ms/o0ukef>
> ------------------------------
> *From:* Fred Habash <fmhabash_at_gmail.com>
> *Sent:* Monday, January 18, 2021 4:39:58 PM
> *To:* Karthikeyan Panchanathan <keyantech_at_gmail.com>
> *Cc:* oracle-l_at_freelists.org <oracle-l_at_freelists.org>
> *Subject:* Re: Creating Nonpartitioned Indexe vs. Partitioned Global
> Prefixed on Nonpartitioned Table
>
> Thank you.
> Have you been able to find any references supporting this use case?
>
> On Mon, Jan 18, 2021 at 4:18 PM Karthikeyan Panchanathan <
> keyantech_at_gmail.com> wrote:
>
> Fred
>
> Used recently for high frequency OLTP database on non-partition table.
> Table index causing high Buffer Busy Waits due multiple sessions doing DML
>
> Created Hash partition global partition index which reduced in half.
>
> I understand we can partition table and have Local index. In my case i was
> advised to minimum change without any impact to application layer
>
> HTH
> Karth
>
>
> Get Outlook for iOS <https://aka.ms/o0ukef>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Fred Habash <fmhabash_at_gmail.com>
> *Sent:* Monday, January 18, 2021 3:57:57 PM
> *To:* oracle-l_at_freelists.org <oracle-l_at_freelists.org>
> *Subject:* Creating Nonpartitioned Indexe vs. Partitioned Global Prefixed
> on Nonpartitioned Table
>
> I have a nonpartitioned table in an application schema that has two
> globally partitioned prefixed indexes. I do not know why the
> original author decided to use partitioned indexes.
>
> I've researched the Oracle docs and there is a description of local vs.
> global indexes. But why would one choose to partition indexes on a
> non-partiioend table?
>
> Overall, global prefixed indexes are good for OLTP, support pruning, and
> they are 'hard to manage'.
>
>
> ----------------------------------------
> Thank you
>
>
>
>
> --
>
> ----------------------------------------
> Thank you
>
>
>

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