Reorganization Methods
Use
When you
reorganize the Oracle database with SAPDBA, you can use either of the following methods if you are reorganizing tables:SAPDBA exports the data to be reorganized into an external file and then imports it back into the database.
SAPDBA creates a new table in the database and transfers the contents of the old table into the new table. Finally, SAPDBA deletes the old table and renames the new one so that it has the same name as the old one.
Features
The following table summarizes the reorganization methods:
Feature |
Data transfer using SQL CREATE ... |
Export and import |
Complexity |
Low because data only processed inside database |
High because data also processed outside database |
Integrity |
Sometimes poor because freespace of tablespace cannot be allocated optimally due to extra copy of object to be reorganized – the result can be fragmentation |
Good |
Scope |
Cannot be used for tables with long columns and when reorganizing data files |
No restrictions |
Security |
Good because entire operation takes place inside database |
Some risk because data is exported to the file system |
Space |
High requirement inside database because copy of object is created |
Inside database: low requirement In file system: high requirement |
Speed |
Fast, especially for transferring a single table to another tablespace |
Slow |