jump to navigation

Possible To Shrink A Newly Created Index ? (Just) January 5, 2009

Posted by Richard Foote in Shrink Index.

OK, first post of the year !!

I thought I might answer a recent question by Alon Principle on my post Index Rebuild vs. Coalesce vs. Shrink Space as it’s an interesting one which deserves a bit of a discussion.

Basically the question is why right after an index has been created has the size of the index reduced after a subsequent Shrink Space command ? Shouldn’t the index be as efficient as possible after it’s been created and yet the index has indeed shrunk with the number of blocks and the overall size of the index reducing ?

Good question !!

Now I was initially a little confused as indeed, after an index has been created, it’s as efficient as the the value of PCTFREE allows. As I discuss in the above mentioned post, a Shrink Space command will reorganise the index in a similar manner to the coalesce but importantly ensure that any freed blocks are at the physical”end” of the segment so that any empty blocks can be de-allocated and returned to the tablespace for reuse by potentially another segments.

Therefore, the Shrink Space command should not reduce the number of leaf blocks for a freshly created index at all …

So I asked Alon for some more information and he kindly provided a demo which immediately told me what the issue was.

BTW, here’s a bit of advice. If someone asks a question and the information provided is not sufficient to provide a meaningful answer, there is absolutely nothing wrong, rude, disrespectful or insulting in asking for more information (as claimed by certain folk I could mention). It’s actually the most appropriate and respectful manner in which to correctly diagnose and provide the most appropriate and accurate answer or solution to a question. When you see a doctor and mention you’re ill, you expect the doctor to ask a few prudent questions to determine what’s actually wrong with you rather than having a doctor say “Oh, I had someone who said they were ill last week, here just take these pills, it worked for them” !! The same goes for our profession …

But I disgress.

OK, the first important thing to note is that to be able to shrink a segment, it must be in an Automatic Segment Space Management (ASSM) tablespace. However, the extent management policy of the tablespace can be either Uniform or Autoallocate, it doesn’t matter. If the tablespace has Uniform extent sizes, then basically all the extents have a uniform (or the same) size, if it’s autoallocate, then Oracle decides the appropriate extent sizes and extent sizes can differ for a specific segment as it grows.

The next important point to note is what do we actually mean by the “size” of an index. Do we mean the actual number of leaf (and branch) blocks that are currently assigned to the index “structure” or do we mean the number of blocks currently assigned to the index “segment” ? It’s an important distinction in this discussion.

In the demo provided by Alon, it showed a new single column table being populated with numbers, a new index being created, with the bytes, the number of extents and blocks in the index “segment” listed from user_segments. The index was immediately shrunk and indeed the bytes and the number of blocks reduced, but the number of extents remained the same.

It’s all I needed, I immediately had the answer to the question (as indeed I now knew exactly what was meant by the question).

The tablespace in question must have been a ASSM tablespace and it was only the size of the index “segment” that had reduced, not the size of the index structure. The index structure however would be exactly the same size as it had previously, with the index just as “efficient” as it had been previously.

Indeed, the Shrink Space command would have gone through the index structure and ultimately would have had nothing to do as each leaf block within the index was packed as efficiently as possible (based on whatever PCTFREE value is set).

BUT, there may very well be some blocks within the last allocated extent that may not yet have been used within the index structure. They currently belong within the last extent assigned to the index segment, but they’re currently above the High Water Mark (HWM) of the segment.

Now, if the tablespace were uniformly managed, then these “unused” blocks can’t be de-allocated and returned to the tablespace, because the extents within the tablespace  need to remain uniform. Therefore there would be no effect of the Shrink Space command on such a segment.

BUT if the tablespace was ASSM with autoallocate, then the extents are controlled and managed by Oracle and can differ in size, both within the tablespace and for a specific segment. Therefore the Shrink Space command can “trim” off the unused blocks in the last extent.

Note if the segment was created with too large a Minextents value and not all the allocated extents have been used, a Shrink Space command could deallocate space from more than one extent, including a Uniform tablespace, but only if entire extents currently exisit above the HWM.

This demo on Shrinking A Newly Created Index expands a little on the demo provided by Alon and shows how the last extent of a freshly created index in an ASSM LMT with autoallocate has been “trimmed” by the Shrink Space command, but has had no effect on an identical segment created in a Uniform LMT. In both examples however, the actual number of leaf blocks in the indexes remains the same as the index structures themselves are as efficient as can be.


1. Alon Principal - January 6, 2009

Great explanation – thank you!


2. Richard Foote - January 6, 2009

Hi Alon

No worries.

One thing I forgot to metion is that if you notice the size of the new trimmed last extent, it’s 104 blocks or equivalent to 13 x 8K block (64k) extents. So although it might look like all this could cause fragmentation, the “holes” are all still multiples of 64K and so can all be potentially reused by other segments.


3. Log Buffer #130: a Carnival of the Vanities for DBAs - January 10, 2009

[…] Foote asked, is it possible to shrink a newly created index? His answer? […]


4. Surachart - January 15, 2009


thank you


5. pc - September 2, 2010

Hi Richard, Indeed a great explanation, thanks.

To me it seems there are some typos (I might be wrong):
1. Believe the foll. line:
“BUT if the tablespace was ASSM, then the extents are controlled”
should have been:
“BUT if the tablespace was ASSM with autoallocate, then the extents are controlled”.

2. “freshly created index in an ASSM LMT has” should have been
“freshly created index in an ASSM LMT with autoallocate has”

3. In your reply to Alon dt. 6th Jan ’09, you have mentioned:
“it’s 104 blocks or equivalent to 13 x 8K block (64k) extents. So although it might look like all this could cause fragmentation, the “holes” are all still multiples of 64K”. Again 13 x 8k is 104 blocks and hence the last trimmed extent is of size 104k and not 64k nor a multiple of 64k.

Kindly let me know if the above is correct or I have lost my senses!



6. Richard Foote - September 9, 2010


Yes, your typo suggestions are correct and have been amended, thank you.

With your point 3, I don’t quite get what you’re saying. The last extent is 104 blocks (not K) in size after being trimmed. 104 is divisible by 8 (it goes into 104 13 times) which means we could effectively have 13 extents, each being 8 x 8K (the block size) in size which equals 64K if this were to be subsequently dropped.

These 13 64K extents can all potenitally be reused by new/growing segments within the tablespace.


7. PC - September 27, 2010

Hi Richard, I get it now. Actually that 8K of “13 x 8K” confused me. Many Thanks.


Richard Foote - October 7, 2010


No worries 🙂


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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: