Database Reorgs:  Why They Matter, and the Difference between On-Line and Off-Line

Database reorgs are performed to save data space and improve database efficiency and performance.  This article explains why. The next article shows how to reorg multiple tables and databases in Eclipse.

Data in large RDBMS tables eventually become fragmented. The size of tables and indexes increase as records become distributed over more data pages. More page reads and rows in non-join order during query execution slow query responses. To reclaim the wasted space, improve database uptime and speed data access (query responses), consider a strategy for reorganizing your database objects.

Database reorgs consist of two types for these table, index and tablespace objects: on-line (in place) and off-line (classic).

On-line database reorgs work incrementally by moving rows within the existing table to re-establish clustering, reclaim free space, and eliminate overflow rows. Objects are unavailable only for a short time near the end, not during the reload and rebuild phases, which can be protracted for large objects. They allow applications to connect to the database, but often slow their performance, and can create lock waits at that time.

Off-line database reorgs are faster, but can take the database off-line (if the database reorg utility is used). With this method, data is exported from the database into a dump file (unload). The database objects set back up based on the extract, typically re-ordered (sort). They are then returned to the same tablespace (load), where indexes are restored implicitly (rebuild).

Performance-conscious DBAs use IRI FACT (Fast Extract) for the unload, which creates a portable flat file that can be sorted (with IRI CoSort) on the primary index key of the reorganized table. With this approach, other transformation and reporting operations can occur, and the database remains on-line. Pre-sorted, direct path loads also bypass the sort (overhead) of the database loader. All these operations are automated in the IRI Workbench offline reorg wizard.

Holding a “shadow” copy of the data in the file system for each table should not be unduly onerous because once the flat file is sorted and re-loaded, it can be deleted. At the same time, having the reorg data externalized and available to CoSort also allows the possibility for other uses of the data, including archival, reporting, protection, and migration to other database, BI tool, and application targets.

The caveat of course is that during the unload, other system users can read and may update the table space, so any updates during this time could miss the re-load and create inconsistencies in the target. It is therefore recommended that off-line reorgs be performed when updates are not occurring.

IRI offers an off-line reorg solution, described and shown here.