No, I said you want to coalesce.
You do not want to shrink.

Because coalesce will just do a reorganization on the existing data, in place and leave any fully free blocks it created belonging to the index - it does not release it, it will not cause the index to have to reallocate it.

see the next couple of comments...

sweeper indexes (my term) are indexes that are on

a) monotonically increasing values
b) and you delete some/most BUT NOT ALL of the old values

can benefit from periodic coalesces IF

c) you select * from t order by that_column (you read the old to new data via the index - as you read the left hand side of the index, you are reading lots of mostly empty leaf blocks)

d) need to reclaim that space since it cannot be reused (point (a) makes it impossible to reuse that left most index block that is almost empty)

nope, locally managed tablespaces are always coalesced, they never need it - regardless of type (uniform or autoallocate)

you are mixing up things here.

there is the ability to coalesce a tablespace. In the olden days of lore, when dictionary managed tablespaces were the only thing going - you sometimes, under some conditions wanted to "coalesce a tablespace" - that would combine adjacent free extents into a single free extent (something SMON does in the background quite nicely, this was just a way to force it to happen right now). It ONLY applied to dictionary managed tablespaces.

So, when you wrote:

Does it make sense to use coalesce with LMT tablespaces? Reading some of the threads, it seems like
if LMT tablespace is uniform extent, coalesce isn't going to do much. What if LMT tablespace is
autoallocate?

It seemed obvious to me you were talking about coalescing a tablespace - since the ONLY THING you mentioned in there was.... a tablespace.


Now, there is also "coalesce for indexes" which walks an index and looks for adjacent leaf blocks that can be combined into a single leaf block (leaving us one 'full' leaf and one 'empty' leaf). The empty leaf block goes onto the free list of the index (regardless of tablespace type) and is removed from the index structure. So, regardless of the underlying tablepace type - an index coalesce would simply combine leaf blocks and take some leaf blocks OUT of the index putting them on the freelist.

In no case would a coalesce return space to the tablepace, it simply puts the blocks on the freelist of the index segment - they still (in all cases) belong to the index.


So, it is as good to coalesce an index in a DMT as an LMT and vice versa. It doesn't matter how the extents are allocated - because we are not talking about managing extents - we are talking about managing blocks.



UNLESS of course, you really meant to say "shrink an index" all along - if that is what you meant then shrink can return 100% of the freed up space to the tablespace (autoallocate locally managed tablespaces - extents DO NOT have to be uniform) or it can return MUCH of the freed up space (uniform allocation). In the latter case - it will not be able to return any extent that contains at least one block of index data - but it would return all of the other extents that are no longer needed...

that do we have benifits of having indexes more
then 60gb in size

sure, you get the same benefits you get from a 1mb index. Faster access to an individual row by some key.

What you might be missing out on would be administrative ease of use - managing a single 60gb index is probably harder than a few 10gb indexes. "it depends"

Are you running into any issues that could be solved by having more "small" indexes?

coalesce would take any empty blocks - and any blocks it makes empty by combining adjacent blocks together - and put them on the process free list.

You should use ASSM - automatic segment space management and be done with free lists and free list groups.

So in this above case i think we don't need to rebuild index as 80% of the data
is fine and our index is not randomly sparsed.

no, this is an index that probably needs attention. Your left hand side is close to empty (but not empty). The right hand side is 100% full. This is what I call the sweeper index - you delete most, but not all, of the old data. Those index blocks on the left hand side will never be reused for space since the sequence is always increasing.



Your second case is the case whereby YOU DO NOT NEED TO REBUILD since we'll have an opportunity to reuse that space all of the time.



coalesce takes adjacent blocks and tries to merge them together into a smaller set of blocks while preserving your pctfree settings. That is all we need to really understand.

now as you told the spaces in left hand side will never be reused so in this
case of sweeper index shall we coalesec so that we release from free blocks or
we will do rebuild to create a new index.

I prefer coalesc, 100% online, doesn't need two times the space and will not cause ora-8103's.


Actually in soem document i have found where they mentioned this case can be
considered for index rebuild.,

actually, did they give any evidence, did they lay out a logical reason, did they demonstrate anything good coming from this long term?

I wrote what I wrote and I'll stick by it.

sweeper indexes are the ones that need attention (indexes on dates, sequences typically)

normal indexes on something like "last name" where the data arrives randomly - in general just leave them alone. They'll always be around 40-60% empty. they like it that way.

http://jonathanlewis.wordpress.com/2008/05/14/index-efficiency/
http://jonathanlewis.wordpress.com/2010/02/28/index-efficiency-2/
http://jonathanlewis.wordpress.com/2010/03/03/index-efficiency-3/

read those

that is not an operation that can be done in parallel, it doesn't support it.

it is just not supported syntax at this point in time.

shrink can release space, coalesce cannot - will not.

but otherwise, they are pretty much equivalent.

Richard - thanks! that was excellent

full index scan reads a block at a time. it only reads the blocks in the index structure, not the ones on the free list.

so no, it would not impact an index full scan.

because we do not necessarily clean out old index entries immediately. Imagine how slow deletes would be if we did (basically reorganize an index after each delete???)


here is a question for you

why the heck would you count rows in a table (and using count(1) makes it look like you don't know sql very well :) why do you want to count the number of "1's" in a table? if you want the count of rows - count(*) is the proper way to do that)