After deleting many rows from a table, we face the next challenge. What can we do do optimize both space usage and performance of this table now that it has much fewer rows? After a large DELETE operation, our table will look like this:
And while swiss cheese is yummy, it's bad for our tables and bad for our database. Now we need to get to work.
- The first thing we want to do is reset the High Water Mark (HWM). The HWM comes into play on full scans since Oracle will scan the blocks up to the HWM. If you had 10 million rows in the table and delete them all, Oracle will still think there's 10 million rows to scan until the HWM is adjusted accordingly. There are different ways that you can reset the HWM. In version 10, you can simply use the SHRINK command although row movement must be enabled for the table. In version 9, it can be a little bit trickier - moving the table will reset the HWM and you can even move it to the same tablespace. However, the tablespace must a locally managed one (LMT).
- The next thing to do is to re-generate optimizer statistics so the optimizer can make more intelligent decisions. This one is easy - a simple call to DBMS_STATS will do the job.
After these two things are done, you're rid of the pesky rows following the SQL DELETE operation and you've put the table back into an optimal state.