jump to navigation

Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones) February 8, 2008

Posted by Richard Foote in Index Coalesce, Index Rebuild, Index Shrink, Oracle General, Oracle Indexes, Performance Tuning.

Previously, I discussed how an ALTER INDEX  … COALESCE is going to be less expensive in terms of using resources than an equivalent ALTER INDEX … SHRINK SPACE COMPACT (or ALTER INDEX … SHRINK SPACE) as the Coalesce doesn’t have to concern itself with ensuring all leaf blocks at the physical end of the index segment have all been moved to allow for the storage to be de-allocated from the index segment. If you just want to de-fragment an index and not necessarily reduce the overall space allocated to the segment, use Coalesce rather than the Shrink options as it’s cheaper.

But what about an ALTER INDEX … REBUILD, when, if ever, should it be used ?

Well the answer is as with most things Oracle, it depends.

Scenario One.

We have a table and the application deletes historical data but in a manner in which leaf blocks are not being entirely emptied. Basically, older stuff is removed, but it’s only removed in a random manner, from approximately the “earlier” 10% of the table. The index is sequenced which means only those leaf blocks in the “left-most” 10% of the index structure are impacted but all this deleted space is “deadwood” as new index entries are only being inserted into the “right-most” part of the index.

Note that basically 90% of the index is fine and very well utilised, it’s only 10% of the index that’s problematic. Of the problem 10% of leaf blocks, there’s plenty of free or deleted space, with many leaf blocks almost but not quite empty.

Coalesce (and indeed Shrink) will basically run through these 10% of fragmented leaf blocks and will merge the index row entries into as few leaf blocks as possible. With the 90% of blocks that are fine, Coalesce will basically read and then ignore them from any processing as there’s nothing that can be done for them.

Rebuild on the other hand will take an entirely different approach. It will (generally) read the entire existing index structure and will build a brand new, bright and shining index segment.  As part of this process, it will rebuild the entire index, it has no choice (assuming the index isn’t partitioned, but that’s another story) and will rebuild the 90% of the index that was actually perfect to begin with. Rebuilding 90% of something that doesn’t need rebuilding doesn’t sound particularly efficient and indeed it isn’t. As a result, the index rebuild will use substantially more resources and generate substantially more redo than an equivalent Coalesce (or Shrink Space).

Scenario Two.

We have an application that deletes data and it deletes data throughout the entire index structure. The deletes are significant with a substantial proportion of the overall rows having been deleted. Additionally, the table is not going to be repopulated with anything like the same volume of data or it won’t be repopulated for a substantial period of time. As such, all this deleted index space is “deadwood” as it’s not going to be used any time soon, if at all.

Now typically in this sort of scenario, it’s of course the table as much as the associated indexes that needs to be rebuilt. That’s a key point. However, maybe Full Table Scans are not an issue for this table so the wasted space in the table is not of urgent concern. Maybe the table in not in an ASSM tablespace or in a database that supports a Table Shrink command and maybe moving the table is not an immediate option due to availability concerns. For whatever reason (or lack of reason), the index needs to be de-fragmented.

Note it’s the entire index that’s problematic here and there could be portions of the index that have very few remaining index entries.

Now poor Coalesce (and indeed Shrink) has a bit of an issue here. They both merge index entries from two blocks into the one block where it can. However, if leaf blocks are really empty, these merged index entries may in turn be merged and moved again with index entries from yet another leaf block. And maybe yet again with another leaf block. And again and again … So a specific index entry may actually be moved into several different leaf blocks during the entire process. Each of these moves requires resources and generates redo and takes time.

Now the rebuild has an entirely different approach. As mentioned, it will basically (generally) read the entire exisiting index structure and will build a brand new one, but importantly as it does so will only have to locate a specific index entry once and once only. Also, as it’s the entire index structure that’s problematic, there’s no issue with fixing the entire index, as it’s all “broken”.

As a result of only having to deal with an existing index entry the once vs. the Coalesce which may relocate a specific index entry many times, the index rebuild is going to be substantially more efficient and potentially use significantly less resources and generate less redo.

This demo of the Differences between a Coalesce, Shrink Space and Rebuild shows when one out performs the other.

Basically, Coalesce is particularly efficient and uses less resources when the percentage of the overall index structure that’s problematic and fragmented is relatively small (less than approximately 20-25% of leaf blocks). Rebuild is particularly efficient when the percentage of the overall index structure that’s problematic and fragmented is relatively large and the average degree of fragmentation within an index leaf block is relatively high.  Note Pre 10g, an index needed to have at least 50% free space less pctfree in neighbouring leaf blocks for a Coalesce to be effective.

Now Rebuild (and Rebuild Online) potentially have locking implications that need to be considered although as we’ll see later, 11g has addressed some of these issues …


1. Robert - February 8, 2008

Once again, interesting stuff! I’ll put this on the whishlist for Oracle 19: augment statistics gathering with additional logic that will detect which of rebuild / coalesce / shrink will be most efficient given the current (say last few days) usage pattern on the index. Add another package that is able to automatically do the optimal reorganization (or none at all) based on the info gathered.

I would have guessed that the rebuild uses the table – but when you think about it a moment the index will likely be smaller than the table so less IO will have to be done this way.

Thanks again!


2. Richard Foote - February 8, 2008

Hi Robert

If Oracle19 does everything for you and all the various indexes structures get automatically created, used, applied, tuned, maintained, partitioned, etc. etc., then I’ll have nothing to write about 🙂

Actually, when I think about it, it may not take as long as Oracle19 for Oracle to get there.

Gee, I hope David Bowie releases a new album soon …


3. Robert - February 8, 2008

I deliberately picked a higher version number to give you a chance to reach retirement age before the release. 🙂

Also, I don’t think you run out of topics to write about: until then Oracle will introduce so many new features (new index types?) that there will be a constant stream of new issues to investigate and educate people about. 🙂 But generally I have the impression that Oracle has decided to improve manageability, at least that’s where I think 10g makes a major leap.

Have a nice weekend – and Listen to the Music


4. Jack - February 8, 2008

We hit a significant bug on with ALTER INDEX REBUILD. See Bug 6455161 “Higher CPU / Higher “cache buffer chains” latch gets / Higher “consistent gets” after truncate/Rebuild”
Note: 6455161.8 (21-DEC-2007).

Apparently it affects through

The issue is that the index root block is pinned and unpinned for each key lookup during a Nested Loop on Indexes that have OBJECT_ID DATA_OBJECT_ID (as viewed in DBA_OBJECTS).
Each pin/unpin operation obtains the “cache buffer chains” latch, uses extra cpu and increases the “CONSISTENT GETS” statistic.

At the individual statement level queries doing Nested Loops with
Indexes lookups may do more “cache buffer chains” latch gets and have a larger than expected “CONSISTENT GETS” statistic (“query” column on tkprof).

At the instance level it is possible to get some contention on the
“cache buffer chains” latch and some increase in the CPU utilization.

The DATA_OBJECT_ID for an index changes if an ALTER INDEX REBUILD or TRUNCATE TABLE operation has been performed on the index.

To find a list of affected objects:

select owner||’.’||Object_name||’.’||subobject_name
from dba_objects
where object_iddata_object_id
and object_type like ‘INDEX%’
order by owner,object_name,subobject_name;

*** that this only affects use of such indexes in a NESTED LOOPS operation. ***

Drop and Recreate all indexes which OBJECT_ID DATA_OBJECT_ID. This synchronizes the two values.
(Note that it is not sufficient to REBUILD the index, it must
be explicitly dropped then recreated)


The bug was quite nasty in our case. The work-around to Drop and Recreate indexes worked like a charm, but wasn’t how I originally planned to spend that Saturaday. After just going through a major tuning exercise with ALTER INDEX REBUILD, which was actually an un-tuning exercise.


5. Index Rebuild 10g « Oracle Scratchpad - February 9, 2008

[…] 9,2008 I’ve written a few notes about the pros and cons of index rebuilds in the past. A comment on Richard Foote’s blog describes a bug in 10g (reminiscent of an old index root block bug in early versions of 8i) […]


6. Virag Sharma - February 9, 2008

How to identified indexes, which need rebuild or Coalesce ?
It will be really great , if you can outline some criteria


7. Richard Foote - February 10, 2008

Hi Jack

Thanks very much for the bug reference, very interesting.

I wish people who say how safe rebuilding indexes are would take some note.

Also people like our friend “Joseph Schwertner” who believes rebuilding indexes is a way to prevent bugs would be somewhat disappointed to hear he may actually be introducing bugs at his university databases.

Thanks again, a point to keep in mind for sure.


8. Richard Foote - February 10, 2008

Hi Virag

The easiest criteria I can quickly and easily give you is it not to rebuild or coalesce any index unless you have a specific, proven reason for doing so.

A slightly longer and more complex criteria can be derived from the list of issues I discuss in the “Rebuilding The Truth” presentation.

However, for the thorough answer, you may just need to come to one of my Index Seminars !!


9. Virag Sharma - February 15, 2008

Thanks Richard, we will come to your Index Seminars , if it held in India.
Recently Craig A. Shallahamer had some Seminars in India, hope soon you will also have some Seminars in India.


10. Richard Foote - February 17, 2008

A very good friend of mine recently presented some material for IBM in India and he had a wonderful time so I would love to visit India one day. Maybe during the next Australian cricket tour 😉


11. Pedro lopes - March 15, 2008

Hi Richard

Do you know if Coalescing the index locks the table in exclusive mode?

We have some massive AQ applications and the AQ indexes gets so fragmentated (before coalescing 2000 buffer gets, after 3 for a query) and seems that is caused by the facts explained in

Note:267137.1 QMON does not perform space management operations on the dequeue IOT in Locally Managed Tablespaces using ASSM or when using FREELIST GROUPs

So seems we need to coalesce the indexes quite often and I cant find anywhere which says this acquires a exclusive lock or not…


12. Richard Foote - March 16, 2008

Hi Predro

We don’t use Oracle AQ but it does sound like a right pain of a bug. It’s a pity metalink isn’t a little more clear on why precisely QMON has these issues.

In answer to your question, coalescing does not lock the table in exclusive mode.


13. pedro - March 16, 2008

thanks Richard

I dont understand why AQ index get so fragmentated anyways, usually when you create an AQ table implictly it creates a composite index of 6 fields and when query it only uses the first two field which is queue name and state. There are only two states, processed and pending, as you can see not very selective fieleds.

As I said after coaleces buffer gets reduces from thousands to 3 to 10. But after several hours or so it rises to hundreds again because enqueue (in serts) and dequeue (deletes) are being run constantly. Do you know why such situation can fragmentate the index so much…?


14. Richard Foote - March 18, 2008

Hi Pedro

The reason is likely explained on pages 102-111 on this index internals presentation of mine:

Click to access index-internals-rebuilding-the-truth.pdf

Pages 132-135 shows what the impact can be.


15. Nitin Joshi - October 29, 2008

Hi Richard,
In one of your topics you’ve mentioned that Deleted entries are automatically reused by oracle wheather it may be same or different value.
Here,initially you says some 10% of Sequence values are being deleted so “mostly” they are going to be of same values ,so why can’t they reuse deleted space in the index?

May be i’m missing something?



16. Richard Foote - November 8, 2008

Hi Nitin

Because all the subsequent inserts go into the right most leaf block within the index structure as all new values are the the current largest value. Therefore deleted space within the other leaf blocks are not reused, unless a leaf block contains nothing but deleted index entries, in which case it’s place on the freelist and recyled.

Yes, an insert in a leaf block would clean out any deleted entries within the leaf block but sometimes there are no subsequent inserts in these blocks.

Hope it makes sense.


17. Alon Principal - December 25, 2008


I’m trying to understand what exactly SHRINK does to index.
When I create a new index and right after the CREATE I run shrink on it – it still reduce index’s blocks and size.

Isn’t CREATE should build a new & efficient tree? How come it can be shrunk?


18. Richard Foote - January 3, 2009

Hi Alon

Shrink basically “re-packs” an index such that it can empty a set of index blocks and deallocate them back to the tablespace storage pool. So providing there’s no activity of the table, then yes a shrink should have no effect after just creating an index.

On what version of Oracle and on what platform did you do this and what was the activity on the table during the index creation ? What type of index and was it created using the online option ?

Can you basically provide more details on your situation ?


19. Alon Principal - January 4, 2009

Hi Richard.
Thanks for the reply.

There’s the information you have asked for:
It’s Oracle installation on Red Hat.

select * from v$version;
— Oracle Database 10g Enterprise Edition Release – Prod
— …

create table SHRINK_INDEX (i number);

n number;
for n in 1..1000000 loop
insert into SHRINK_INDEX values (n);
end loop;



— BYTES: 18874368
— BLOCKS: 2304

alter index SHRINK_INDEX_IDX shrink space;

— BYTES: 18677760
— BLOCKS: 2280


20. Richard Foote - January 5, 2009

Hi Alon

I thought it worthwhile to write a blog piece (Possible to Shrink A Newly Created Index) to give the answer to your question justice.


21. Mark - September 2, 2009

Interesting read, thanks for the post.

What do you think about using the automatic segment advisor findings to determine when to do a shrink/coalesce/rebuild?

select * from table ( DBMS_SPACE.ASA_RECOMMENDATIONS )
where segment_type like ‘%INDEX%’


22. Richard Foote - September 6, 2009

Hi Mark

I think it gives some indication on what space savings you may have which can give an indication of which way to go. But generally it’s shrink if you want to permanently reduce the size of a segment and as such return storage back to the tablespace, coalesce for most other scenarios unless space fragmentation is really bad throughout the index structure in which case a full rebuild might be needed. A treedump can often provide the necessary additional information.


23. Steve Howard - January 28, 2010

Hi Richard,

Am I correct in thinking that the essence of the pro’s for a rebuild in this case is the time/resources it requires to do so? In other words, after either a shrink or a rebuild is complete, are there any other differences?




Richard Foote - February 3, 2010

Hi Steve

Yes, the main “pro” is the resources and locking implications required to do rebuild vs. coalesce vs. shrink.

However, a rebuild can potentially reduce the height of an index in an extreme case whereas a coalesce will never do so.

Additionally, after a fresh rebuild stats are automatically collected, not during a coalesce.

Also, a rebuild will more likely have adjacent leaf blocks that are “physically” adjacent that in an extreme case may make a tiny difference.


24. Michael Kreuz - December 17, 2010

Very nice. I have not found that much information on index block splitting, and this was great information and the explanations very insightful.

Thank you very much.



Richard Foote - December 21, 2010

Hi Mike

Glad it was useful 🙂


25. fabio - October 19, 2011

This post is very usefule.


26. Stalin Subbiah - July 28, 2013

Hi Richard,
Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon? We are simply purging data older 6 months so no partial deletes.

I wanted something less intrusive since this will be performed on an active production system, so validate index is out of the door. Does block dump next to header block has this info?

We aren’t doing fast full scans or in any space pressure, so don’t plan on rebuilding it anytime soon but curious to see how are they being reused.

Appreciate your response.



Richard Foote - August 1, 2013

Hi Stalin

There are a number of possible ways but one easy method is to simply use the DBMS_SPACE package, specifically SPACE_USAGE or FREE_BLOCKS (depending on whether you’re using ASSM or Freelists) to display the number of index blocks with differing levels of free space. After a large delete, you’ll see the number of blocks with free space/labelled free increases and then decreases again after subsequent inserts.

I’ve had a quick check through my blog and I don’t find a reference to having written about this before so I might whip up a quick demo and post it for you as it’s a good question.


Stalin - August 2, 2013

Thanks Richard as always. Glad you pointed out dbms_space.space_usage, as i ended up using that after doing some research. In comparing the stats from yesterday vs. today, FS2 blocks seems to have gone up from 1.46Million to 1.88M. Full blocks have gone down from 7.52M to 7.10M. The unformated blocks of 1.3M remains the same from both days snapshot. FS1,3, and 4 are zero blocks.

Interesting to see these stats, as i was expecting FS4 blocks to be a high number and not FS2 from the nature of deletes we do. The index is on Id, name col where Id being FK based on sequence.

Also since unformated blocks haven’t be used, i assume free blocks from free lists are being reused ? it doesn’t make sense from these numbers though. Am i way off from my interpretation?



27. My Possible Pasts » Blog Archive » Index Health Check - May 13, 2014

[…] a good candidate for being rebuilt (or coalesced or shrunk.. for info on which to choose, check out this post by Richard Foote). Jonathan being his normal generous self, shared this script in the presentation. This is a huge […]


28. Oracle Database 19c Automatic Indexing: Predicted Back In 2008 (A Better Future) | Richard Foote's Oracle Blog - March 19, 2019

[…] I’ve recently received a number of correspondences regarding one of my most popular blog posts, dating back to February 2008: Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones). […]


29. Same time even after deleting the row - December 4, 2022

[…] shrinking the table or its indexes would reduce the number of blocks that need visiting and therefore speed up the query. At the […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: