Re: ASSM and Indexes
Date: Sun, 03 Jan 2010 10:29:32 -0700
Message-Id: <201001031729.o03HTWPF005715_at_mail96c0.megamailservers.com>
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.
At 09:09 PM 1/2/2010, Tanel Poder wrote:
>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.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 03 2010 - 11:29:32 CST