Database Actions to Take Effect After Archiving or Deleting

Oracle and Informix: Reorganize index

If data has been archived or simply deleted and the associated tables were accessed via an index, the index should be reorganized. Deleting table entries leaves holes in the table which are still indexed. Reorganization can shorten the access paths, reducing response times.

For databases with a cost-based optimizer: Update the database statistics

If your database uses a cost-based optimizer, you must choose Update Statistics to recalculate the access paths.

Oracle and Informix: Reorganize tablespace or database space

Whether you should reorganize the tablespace depends on the reason for archiving.

?      Do you expect a lot of new data for the archived tables?

If so, you should not reorganize.

If, on the other hand, you archived data that is no longer needed in the system and the table is otherwise rarely changed, you should reorganize.

?      Do you want to make space for other tables?

If so, you should reorganize the tablespaces/database space of both the tables and the indices.

This procedure is described in the SAP NetWeaver AS document SAP Database Guide: Oracle and Informix (BRSPACE for Oracle and SAPDB for Informix databases) and the ABAP Dictionary, section Tablespaces and Extents (database-independent parameterization, MaxDB).

See also:

Oracle:

Space Management with BR*Tools

Segment Management with BR*Tools

Informix:

Dbspaces with SAPDBA

Reorganization with SAPDBA