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<mailto: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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Fred Habash <fmhabash@xxxxxxxxx<mailto:fmhabash@xxxxxxxxx>>
Sent: Monday, January 18, 2021 3:57:57 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
<oracle-l@xxxxxxxxxxxxx<mailto: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