Table Reorganization

DBAs perform table reorganization when the table size growth and becomes fragmented over the large number of extents on the hard drive, which leads to SQL query performance degradation. Also the database server does not automatically releases the physical space consumed by deleted rows in the table. As a result, when the number of deleted rows growth, the data in the table has to be manually compacted, to free the extent space occupied by deleted rows.

The Table Reorganization wizard allows you to choose between the following two table reorganization methods:

• exporting data into external text file, dropping and recreating the table with modified parameters, importing data from the text file, recovering all dependent objects, foreign key relationships and user permissions
• reorganizing table 'in-place' by means of ALTER TABLE SQL statements, which force table data to be physically reallocated and compressed.

The second method is faster and simpler because it does not require data export and import operations and also does not affect dependent objects/user permissions but it requires the table dbspace to have at least as much free space as the current table size. Also the size of the first extent for a table can not be modified using this reorganization method.

The first method allows to modify the first extent size parameter and it does not require an additional space in the table dbspace but it is slower because it involves exporting/importing of the table data and less safe because dropping of a table implicitly removes dependent objects, such as views, triggers, foreign key constraints from other related tables and user permissions. If performed manually, this reorganization method requires a number of steps, which have to carefully performed in the exact order:

1. Unload data to a file and make sure that this operation was successfully completed.
2. Find out, which other tables use this table as part of primary/foreign key referential integrity and create recovery SQL script for those tables FOREIGN KEY constraints.
3. Create the SQL script, which will recover existing table indexes, triggers, views and synonyms.
4. Create the recovery script for the existing user permissions for this table.
5. Drop the table using DROP SQL statement
6. Recreate table using new parameters for CREATE TABLE SQL statement. During this operation you can change first/next extent size, move to a different dbspace, fragment across multiple spaces, etc.
7. Load data back into the table.
8. Recreate all indexes, triggers, synonyms and views for the table using SQL statements.
9. Recreate all foreign key constraints for other tables, which reference this table using primary/foreign key referential integrity.
10. Recreate user permissions.

The Table Reorganization wizard performs all above tasks automatically and also creates you a full script for the operation. You can choose to run this script from the wizard itself or run it using other tools.