Have you considered a global hash-partitioned index on (a) instead? That should distribute the contention N ways on insert/update. The flip side is you would have to update global indexes on partition operations, but that may be preferable to hot block contention. -- Adam Musch ahmusch@xxxxxxxxx On Mon, May 10, 2010 at 9:54 AM, hrishy <hrishys@xxxxxxxxxxx> wrote: > > Hi > > The application also uses selects with range predicates and that pretty > much rules out a reverse key indexes. > > The index block is hot because there are multiple sessions which perform a > insert into this table. > > > --- On *Mon, 10/5/10, troach@xxxxxxxxx <troach@xxxxxxxxx>* wrote: > > > From: troach@xxxxxxxxx <troach@xxxxxxxxx> > Subject: Re: Index as hot block > To: hrishys@xxxxxxxxxxx > Date: Monday, 10 May, 2010, 13:36 > > Try a reverse key index. > Sent from my Verizon Wireless BlackBerry > ------------------------------ > *From: *hrishy <hrishys@xxxxxxxxxxx> > *Date: *Mon, 10 May 2010 11:08:52 +0000 (GMT) > *To: *<oracle-l@xxxxxxxxxxxxx> > *Subject: *Index as hot block > > Hi > > I have a partitioned table called mytable (a,b,c,d,e,f) partitioned by > range on column a. > I need to enforce a unique constraint > currently the uniqueness is enforced by creating a locally partitioned > index on > myindx(c,b,a) > and this has become a hot block. > > I was thinking of rebuilding that index and using myindx(a,b,c) > can you guys let me know what your thoughts are ? > > regards > > >