Possible To Shrink A Newly Created Index ? (Just)

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.