When using tables with large amounts of rows and especially after a lot of rows have been deleted from such a table, reorganizing the table may improve the performance of the table.
Oracle knows two types of table reorganizations.
- Rebuilding the table by means of recreating it
- Rebuilding the table by shrinking its free space ( Oracle 10g and up )
Below the two methods are explained.
Rebuilding the table by means of recreating it
There are two ways of rebuilding a table by recreating it. The first option is to export all the data into a file using the export utility.
After that truncate ( of recreate ) the table and reload the data back into it. The disadvantage here however is a long downtime.
Another method is moving the table either to another tablespace or within the same tablespace.
The advantage here is that the downtime will me much less, but it also has some disadvantages:
- The tablespace needs to be able to store this second copy of the table
- The tables indexes, any depending objects like packages, procedures can become INVALID and might need to be rebuild
- There will be a table-level exclusive lock on the table involved. Any application using that table will be affected by this lock.
By moving the table to another tablespace temporarily, the DBA is also able to reorganize the tablespace.
Rebuilding the table by shrinking its free space
Starting with Oracle 10g all the above is replaced by a very nice feature called 'SHRINK SPACE';
As of this release, tables can be reorganized online, without affecting the users functionality.
That is:
* The table itself must be in an ASMM tablespace
* The table must have row movement enabled
* At the end of the action a shortt table level lock takes place to adjust the HWM (Highwater Mark) of the table.
And it's easy !!
Just enable 'row movement' and start shrinking tables:
SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;
This enables row movement of the table. THis is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.
SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT;
This shrinks the used space of the table, but does not update the HWM. This is usefull if you don't want to have a table-level lock during business hours.
SQL> ALTER TABLE MYTABLE SHRINK SPACE;
This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM.
SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE;
This command shrinks the contents of the table and all dependent objects like indexes.
High Water Mark:
Now we have mentioned the High Water Mark (HWM) a couple of times. A short explanation:
The HWM is the pointer to that location of a table where on any point in history the table has grown to. If a lot of rows are deleted from a table, a lot of free space exists before the HWM. Doing a full table scan Oracle will not only read the actual rows, but also the empty space up to the HWM.
This is wasted time and is worsening the performance of getting data from that table.
So if from a large table a lot of rows are deleted, e.g. a count(*) will still take as long as before the delete.
Triggers and indexes:
In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.
