What is table fragmentation, and should I worry about it ?

The Oracle (tm) Users' Co-Operative FAQ

What is table fragmentation, and should I worry about it ?

What is table fragmentation?

Table fragmentation is the situation that develops when a table has been created with an INITIAL and NEXT extent sizes which are too small for the amount of data that is subsequently loaded into the table. In essence, the table ends up with a large number of extents rather than just a few.

Should I worry about it?

Short answer, no.

Quote from Practical Oracle 8i by Jonathan Lewis, Page 150 : Let's start by reviewing one of the oldest myths, and biggest wastes of DBA time, in the Oracle book of legends. Someone once said, "You get better performance if each object is stored in a single extent." It must be well over 10 years ago that Oracle published a white paper exploding this myth, and yet the claim lingers on.

The white paper referred to is (probably) How to stop defragmenting and start living: The definitive word on fragmentation by Himatsingka and Loaiza which describes the SAFE methodology for storage allocation. SAFE is Simple Algorithm For Fragmentation Elimination. In this white paper they explain how testing has shown that under normal circumstances, Oracle can quite happily handle segments which have many thousands of extents.

Of course, when dropping or truncating a segment which has many extents, each extent will have to be de-allocated and the dictionary updated and this will have a degrading effect on performance.

Steve Adams of www.ixora.com.au fame, goes on to clarify and extend the SAFE system a little in his article on managing extents. (See below in further reading). From Steve's article, it appears that allowing the number of extents to exceed 500 (for an 8K block) will cause cluster chaining in the dictionary. This will then have an effect on allocating and deallocating extents.

Summary

• Don't worry about having more than one extent in a table.
• Try to keep the number of extents to a managable limit - I personally follow Steve Adams advice and keep extents around the 500 mark.
• Try to keep segmnents of a similar size and 'hit rate' together in separate tablespaces.
• Don't spend your life exporting and importing table to get them back into a single extent - it really isn't worth it !

Further reading:

SAFE - How to stop defragmenting and start living
www.ixora.co.au - Planning Extents
Practical Oracle 8i - Building efficient Databases by Jonathan Lewis. Published by Addison Wesley. ISBN 0-201-71584-8