Is there any way to check whether a table or index has been fragmented?

How to check if table is fragmented or not?

How to check if index is fragmented?

I searched Google but didn't get satisfactory answers. Some points like explain plan, table fragmentation, index rebuild etc I got. But what should be our first step and how should be resolve. How step by step we can move forward to resolve the issue?


You need to use INDEX_STATS view to determine fragmentation.

Firstly, You need to populate this view with an individual index by using :

SQL> analyze index <your_index> validate structure;

and then look at these values ratio and height :

select round((del_lf_rows/lf_rows)*100,2) ratio, height, lf_blks, lf_rows 
  from index_stats;

If ratio is more than %20

or height is more than or equal to 4

or lf_rows is lower than lf_blks,

then you need to rebuild your index against fragmentation.

The blevel column of dba_indexes table is my main point of interest. This gives the number of branch levels (including the root node) for a b-tree index, and the column height is closely related to blevel.

If you have access to MOSC, search for the blevel keyword, especially the subject Index Health. The Oracle Community was also suggesting rebuilding indexes for indexes have blevel more than or equal to 4.

  • 1
    The Oracle Community is fighting the misinformation of "when to rebuild an Oracle Index". The correct answer is "when the index is broken or you have benchmarks to prove you need it". It is taking me some time to collect the correct URLs for my answer. – Michael Kutz Sep 4 '18 at 9:51

You can find fragmented table using below query

SQL> select  table_name,round((blocks*8),2)/1024 "size (mb)" ,
round((num_rows*avg_row_len/1024/1024),2) "actual_data (mb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024/1024),2)) "wasted_space (mb)"
from  dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024/1024),2))
order by 4 desc;

And Use below for find fragmented Index. 1. Execute below


Once above done you can get index analyze report in report from INDEX_STATS table using below query.

  SQL> SELECT name,height,lf_rows,lf_blks,lf_rows_len,lf_blk_len,del_lf_rows,distinct_keys,used_space/1024/1024 used_space


This INDEX_STATS table contain only 1 row. so to get multiple indexs report you have to run one by one and get report after each run.


the query is not correct because tuo are comparing kbytes with mbytes. thi is the correct one:

select owner, table_name,round((blocks*8),2)/1024 "size (mb)" , round(((num_rows*avg_row_len)/1024/1024),2) "actual_data (mb)", (round((blocks*8),2)/1024 - round(((num_rows*avg_row_len)/1024/1024),2)) "wasted_space (mb)" from dba_tables where (round((blocks*8),2)/1024 > round(((num_rows*avg_row_len)/1024/1024),2)) order by 4;

  • You probably should just edit the incorrect answer. – mustaccio Apr 9 '20 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy