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@xxxxxxxxx> 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@xxxxxxxxx>
*Sent:* Monday, January 18, 2021 4:39:58 PM
*To:* Karthikeyan Panchanathan <keyantech@xxxxxxxxx>
*Cc:* oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Fred Habash <fmhabash@xxxxxxxxx>
*Sent:* Monday, January 18, 2021 3:57:57 PM
*To:* oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
*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
