Re: Index as hot block

  • From: Adam Musch <ahmusch@xxxxxxxxx>
  • To: hrishys@xxxxxxxxxxx
  • Date: Mon, 10 May 2010 10:02:16 -0500

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

Other related posts: