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

Thank you both. Nice to hear from you, Mark. I was able to develop a test-case using our schema to reproduce the BBW event. In a nutshell, sessions (high frequency & concurrency) running INSERT statements will benefit from such a partitioned index even though the table itself isn't.

On Mon, Jan 18, 2021 at 6:01 PM Mark J. Bobak <mark_at_bobak.net> wrote:

> 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
>>
>>
>>

-- 

----------------------------------------
Thank you

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