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

Yup, that sounds about right..... :-)

On Wed, Jan 20, 2021 at 8:45 AM Fred Habash <fmhabash_at_gmail.com> wrote:

> 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