-- Thanks for the question regarding "Shrink and coalesce", version 10.2.0.3

 

Submitted on 29-Dec-2008 15:04 Central time zone

| |

Last updated 3-Jul-2012 19:24

You Asked

Tom,
What is the difference between shrink and coalesce and under what conditions should we use each of these options? Do these commands lock the objects?

Thanks...

and we said...

talking about indexes...


shrink can release space back to dba_free_space

coalesce does not.

that is the main thing here.

but if you ask me, you want to coalesce - because that index will need that space again soon in all probability.

coalesce is very "lock friendly" - no locking.
shrink does take a lock during the space release time.

Reviews

December 29, 2008 - 5pm Central time zone | | Reviewer: A reader  Tom,   "...but if you ask me, you want to coalesce - because that index will need that space again soon in all probability. "   Did you mean to say that we do NOT want to coalesce since index might need the coalesced space again?   Thanks...   Followup   December 31, 2008 - 8am Central time zone: 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.   December 30, 2008 - 5am Central time zone | | Reviewer: A reader from Germany Tom has expressed his point very clearly - you WISH to coalesce, but do NOT wish to shrink.   Coalesce does not return space back to DB, it stays reserved for your index and so may be reused without new allocation.     December 30, 2008 - 9am Central time zone | | Reviewer: A reader  Thanks...that clarifies the point. Is there a way to figure out if an index should be coalesced or can I do it on a schedule?   Thanks...   Followup   January 5, 2009 - 9am Central time zone: 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) Use COALESCE for Indexes on monotonously increasing values, after large deletes   January 2, 2009 - 1am Central time zone | | Reviewer: Hemant K Chitale from Singapore One rule I would advocate is that COALESCE makes very good sense when you have an index on a monotonously increasing value (eg a Sequence or a Date) and you have a regular job that "purges"  older records (ie the lowest range of Sequence or Date values). COALESCE would be much more appropriate than REBUILD.   Measure and understand why   January 2, 2009 - 5am Central time zone | | Reviewer: Carsten Braess from Germany Actually you may only want to coalesce if you have an index on an increasing value AND your delete job leaves a long tail of sparse records behind. Only in this case it might happen that you benefit from rearranging index entries.   If you delete on a simply policy (e.g. anything older than 90 days) you don't need to do anything. Oracle will recycle the free index blocks.   You can and should measure this via two range scan selects on different ends of your table. For the same amount of data they should take roughly the same amount of gets. But don't forget to take into account how often that data is actually queried: It makes no sense to do daily coalesce when every 3 months a report is run on the old data.     Thanks...!!   January 5, 2009 - 3pm Central time zone | | Reviewer: A reader  A big thanks to Tom, Hemant and Carsten for clarifying the point.   Coalesce with LMT   September 22, 2010 - 11am Central time zone | | Reviewer: A reader  Tom, 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?   Thanks...   Followup   September 23, 2010 - 10am Central time zone: nope, locally managed tablespaces are always coalesced, they never need it - regardless of type (uniform or autoallocate) Coalesce with LMT   September 27, 2010 - 2pm Central time zone | | Reviewer: A reader  Tom, Please correct me if I am wrong. As I understand, coalescing an index in a LMT tablespace will not return any space back and since LMT tablespaces don't have fragmentation, coalesce will not help with that either. What would be the reason for running coalesce against an index in a LMT tablespace?   Thanks...   Followup   September 27, 2010 - 3pm Central time zone: 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... I did mix up   September 27, 2010 - 7pm Central time zone | | Reviewer: A reader  Thanks for the detailed clarification. I did mix up tablespace and indexes. I did mean coalesce and not shrink. Coalesce was still a bit magical to me till you explained it so nicely.   index coalescing   July 27, 2011 - 7am Central time zone | | Reviewer: Amit kumar from India hi Tom,   i got my concepts broaden after going through your threads. one thing i would like to ask, that do we have benifits of having indexes more then 60gb in size. please help me to get out from this curiosity becoz, i am juggling with such large indexes in real time production servers. should i make partitioned indexes in these case. please suggest.   Thanks       Followup   July 28, 2011 - 7pm Central time zone: 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? where does the released block(s) go?   February 12, 2012 - 7am Central time zone | | Reviewer: indhar from chennai,india Hi Tom, When we do batch inserts and deletes serially, I am assuming the leaf-nodes which have all the keys deleted would be placed in the PROCESS freelist ( with the two-way link intact) TILL such time when a leaf-node is reused ( by another transaction) whereupon the blocks become squeaky clean. Would a coalesce just clean up the remaining links and let those blocks remain in the same PROCESS freelist or release it to Master freelist?   Followup   February 13, 2012 - 8am Central time zone: 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. Index coalesec   May 9, 2012 - 8am Central time zone | | Reviewer: Bhaskar from INDIA I have a question on the way coalesec works.   Suppose we have an index in a sequenced column and every day we are deleting 20% of old entries in this index.Again some process als inserts data which increasesvalue of the sequence. So the left hand side of this inde will always be empty.   So in any day we will have 20% deleted space on left hand side and 80% data in this index.   Can you please correct me if my below assumption is wrong?   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. Here we can do coalesec to recover the free space. Now if we do so if say 2 leaf blocks are emptied for 20% deletion in the left hand side and there are more 10 blocks for 80% data - then will coalesec put these 2 block in free list?   In another scenario if we have an index which gets randomly sparsed and everday and some process will also insert data randomly but deletion rate is higher.     Can you please correct me if my below assumption is wrong?   So in this case i think rebuild wll be better because our index is heavily randomly sparsed and rebuild will create a new index.   Please correct me if i'm thinking wrong in any of the above assumptions.   Also I want to know how coalesec works? Say 70% of a leaf block is free and the adjacent leaf block is 20% free.Then will all the entires in second block will come into first leaf block and put the second block into freelist?   I'm using oracle 9i and pctfree is 30%.   Thanks in advance   Followup   May 10, 2012 - 3am Central time zone: 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. Index coalesec   May 10, 2012 - 4am Central time zone | | Reviewer: Bhaskar from India Hi Tom,   "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... "   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.   "Your second case is the case whereby ..."   Actually in soem document i have found where they mentioned this case can be considered for index rebuild. Thnaks for the clarification. So in the above case shall we again do coalesec to free up some blocks and make index compact?   If we put the index as randomly sparsed i think we may have some performance problem?   Thanks for the clarifiaction on the coalesec process.     Followup   May 10, 2012 - 8am Central time zone: 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. Index health monitoring   May 10, 2012 - 10am Central time zone | | Reviewer: A reader  Tom, Given the amount of discussions on indexes and indexes being such important data access structures, is it possible to build a script to monitor the health of indexes (at least B*tree indexes)? The script could analyze indexes and recommend which indexes can be left alone, which would benefit from shrink, which would benefit from coalesce etc.   Thanks...   Followup   May 10, 2012 - 3pm Central time zone:     read those   May 11, 2012 - 12am Central time zone | | Reviewer: Bhaskar from INDIA Hi Tom,   Thanks for the clarifications. That's why i always cross check all fishy solutions here.   shrink space parallel not working   July 3, 2012 - 4pm Central time zone | | Reviewer: kumar from Edison, NJ Hi Tom,   I am trying to run shrink space for an index(db version: 11gr1) with parallel clause. To my surprise it's not allowing parallel clause.   ALTER INDEX SCOTT.TEST SHRINK SPACE PARALLEL 12                                       * ERROR at line 1: ORA-10630: Illegal syntax specified with SHRINK clause   Is it a bug? or why oracle wouldn't allow parallel   Followup   July 3, 2012 - 7pm Central time zone: that is not an operation that can be done in parallel, it doesn't support it. shrink space parallel not working   July 3, 2012 - 6pm Central time zone | | Reviewer: kumar from Edison, NJ Hi Tom,   Adding to the above question, coalesce command does allow parallelism but not for shrink. I believe both coalesce and shrink does same thing except shrink would release the space. Can you please let me know what is that restricting the oracle to not allow parallelism on shrink?   Followup   July 3, 2012 - 7pm Central time zone: it is just not supported syntax at this point in time.