Compile Database Objects - 2

I have explained about compiling invalid objects in my earlier post for the db objects like packages, functions, views etc.

There are some object types that has a little different approach, specific to object types.

I will start with indexes.

Table Fragmentation

The delete from the table deletes the rows but doesn't frees up the memory neither changes the high water mark.

The best way to get the memory cleared is truncating the table, but this hardly gets done in practical systems, and most of the time the delete and insert operations takes place.

So due to delete operations table gets fragmented, and in simple words its size appears more then the actual size of data it has. ( This will appear only if the compression is not enabled, check compression in dba_tables against the table).

Below query can help in identifying the tables which has different tablesize and actualsize, order by the maximum of difference.