Re: ASSM and Indexes

 >>>>and I suppose Hemant's - is: "What, if any, is the benefit of ASSM for indexes?"

YES. What is the benefit of ASSM for Indexes *is* my question.

ASSM specifically relates to FreeLists -- ie replacing a FreeList(s) with a BitMap of available blocks. For an Index, generally, a new entry can only go into a specific leaf block -- unless it is the rightmost entry and the rightmost block is "full" in which case any one free block is used. If there are concurrent inserts with close key values, they contend on the same leaf blocks --- unlike tables where concurrent inserts can be distributed to separate blocks. (Of course, Reverse Key indexes is one "solution" but fraught with it's own issues !).

So, what value add does ASSM provide for Indexes ? If I create tables and indexes in the same tablespace, my indexes are in ASSM. If I create a dedicated index tablespace, it still "defaults" to ASSM. I couldn't find anything in the 10g and 11g concepts which explains how ASSM relates to Indexes.

(Since ASSM is the default, it may become necessary for certain "features" --- eg the ALTER TABLE SHRINK with ROW MOVEMENT enabled works only in ASSM, although I think it could have been written for MSSM as well).

At 01:29 AM Monday, Wolfgang Breitling wrote:
>Unless I'm totally mistaken, the only time Oracle needs to look for
>a free block during index inserts is for block splits. Unlike for
>tables, there is no choice of where to insert a new entry. So my
>question - and I suppose Hemant's - is: "What, if any, is the
>benefit of ASSM for indexes?" Other than consistency - having all
>tablespaces ASSM regardless of content. Is there, for example, a
>benefit if you have massive deletes with a high probability of
>ending up with completely empty blocks that can be re-used followed
>by massive inserts of very similar data both in volume and content.
>
>
>Wolfgang Breitling
>Centrex Consulting Corporation
>http://www.centrexcc.com

Hemant K Chitale

http://hemantoracledba.blogspot.com

--
http://www.freelists.org/webpage/oracle-l