Re: ASSM and Indexes

From: Tanel Poder <>
Date: Sun, 3 Jan 2010 12:09:00 +0800
Message-ID: <>

One thing is for sure - ASSM does not magically spread inserts around into many blocks in "right hand" indexes. You still need to follow the index structure when inserting lots of rows into ever-increasing (or -decreasing) columns. So you would still have a very hot "right hand" block in the index structure when inserting. That's where partitioning, reverse key or function-based columns in indexes still should be used if dealing with high insert concurrency (or RAC).

As the name says ASSM is just for segment *space* management, so you wouldn't need to tune free block tracking (freelists and freelist groups) manually.

If we leave all the bugs out, then if you do use ASSM for your tables, then there's no reason not to use them for indexes as well. However I know few shops who have went back to freelists because constant trouble they had with ASSM. Nevertheless, I default to ASSM nowadays (as we need to tune our own time usage as well !!!) and look into freelists usage only on case by case basis.

Tanel Poder

On Sun, Jan 3, 2010 at 1:49 AM, Wolfgang Breitling

> I am having the same questions/doubts about ASSM for index-only
> tablespaces. If you find out anything definitive I'd appreciate a note.
> Or, better still, summarize it in a post to the list for the benefit of
> all.
> TIA and Happy New Year
> At 08:18 AM 1/2/2010, Hemant K Chitale wrote:
> I am attempting to understand and explain ASSM being used for a Tablespace
>> that is dedicated for Indexes.
>> I can understand ASSM and it being an alternative to FreeLists and PCTUSED
>> for *Tables*.
>> But how is it relevant to Indexes ? How does it apply to Indexes ?
>> Has anyone written a note or come across a note on this ?
>> I have seen a number of bugs relating to performance and other issues --
>> some with Indexes in ASSM.
>> But not an explanation of how and why ASSM would be relevant (or wrong !)
>> for Indexes (other than the Bugs where the workaround was to move the index
>> to MSSM).
> Regards
> Wolfgang Breitling
> Centrex Consulting Corporation
> --
-- Tanel Poder --
Received on Sat Jan 02 2010 - 22:09:00 CST

Original text of this message