One of our Oracle Consultants shared a story with me today that illustrates the importance of periodically reorganizing objects in an oracle database.
He was examining a performance problem with a very small oracle table. The table had only 154 rows!. Hardly a large table, and yet it was taking over a minute to do a full table scan.
After some investigation, he found that in the past the table had over a million rows. These rows had been deleted but the table was never reorganized to remove the unused space. The table was taking up over 130,000 blocks of space, for 154 rows.
Not only was this a great deal of wasted space that could have been used for something else, it also caused a serious performance problem.
He copied the table to a temp table, truncated the original, and then reimported the table data to reorganize the table, and the performance problem was solved.
There are a few methods that can be used to reorganize an Oracle table. These include:
- Using ‘Create table as select * from …’, truncating the table, and then re-inserting from the copy, as was done in this case.
- Using export/import to dump the table out to an external file and then bring it back in.
- Unload the data to a flat file and use sqlloader to bring it back in.
Each of these options has its pros and cons and we will explore these in more detail in an upcoming white paper.
The important thing to remember is that no matter how you reorganize your database objects, best practice is to be sure that this is done on a periodic basis to avoid space and performance problems.