Skip to Main Content
  • Questions
  • Index fragmentation - REBUILD Vs SHRINK SPACE

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nikhil.

Asked: January 25, 2017 - 9:35 am UTC

Last updated: January 26, 2017 - 12:39 pm UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Chris/Connor,

We have gather list of tables/Index along with Allocated space, Used space and %fragmentation.
Could you please help to how do analysis on Indexes e.g. based on allocated/used space which index we may need to REBUILD or SHRINK.

If you could explain 2 or 3 index from the list shared in TB_FRAGMENT_REPORT (LiveSQL - https://livesql.oracle.com/apex/livesql/s/ehersermzjn8mqdiae3h73emd ), I will follow the same for rest.

with LiveSQL Test Case:

and Chris said...

An index is only really "fragmented" if there's empty space in it you're not going to reuse. This tends to only happen with increasing values (sequence PKs, insert date and the like) where you delete old values.

For example, here's a table with two indexed columns. One with increasing values. The other holds values 0 - 100:

create table t (
  x int generated as identity,
  y int
);

insert into t (y) 
  select mod(rownum, 100)  from dual
  connect by level <= 100000;
  
commit;

create index ix on t(x);
create index iy on t(y);

select segment_name, bytes from user_segments
where  segment_name in ('IX', 'IY');

SEGMENT_NAME  BYTES      
IX            2,097,152  
IY            2,097,152  


After deleting most (but not all!) of the rows with x < 90,000, the indexes are still the same size but have lots of free space in them:

delete t
where  x < 90000
and    mod(x, 101) != 0;

89,108 rows deleted.

commit;

select segment_name, bytes from user_segments
where  segment_name in ('IX', 'IY');

SEGMENT_NAME  BYTES      
IX            2,097,152  
IY            2,097,152  


Now here's the thing: index entries have to go in the "correct" place in the index. The values for X always increase. So Oracle can't reuse the mostly empty blocks in IX. But the values for Y stay in the same range. So this can reuse space.

Insert the next batch of rows and you see IX doubles in size, but IY only increases ~50%:

insert into t (y) 
  select mod(rownum, 100)  from dual
  connect by level <= 100000;
  
commit;

select segment_name, bytes from user_segments
where  segment_name in ('IX', 'IY');

SEGMENT_NAME  BYTES      
IX            4,194,304  
IY            3,145,728  


So the percentage of free space isn't useful on its own. You also need to ask "am I likely to reuse that space?"

You can find further discussion about this by Richard Foote:

https://richardfoote.wordpress.com/2008/10/28/fragmented-indexes-due-to-large-number-of-duplicate-entries-more/

And Franck Pachot:

http://blog.dbi-services.com/how-i-measure-oracle-index-fragmentation/

Note: if you want to reclaim space in an index, you could do a coalesce instead of a shrink:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:1388955800346448370

Is this answer out of date? If it is, please let us know via a Comment