How To: Compress the geodatabase
In versioned feature classes or object classes, changes are not applied directly to the business table; instead, they are stored in a pair of tables known as the 'adds' and 'deletes' tables. These tables are created for each versioned feature class or object class, and are included in all queries against a versioned feature class or object class. Because the number of rows increases in these tables as edits are made, performance can degrade over time. Compressing your database will remove unreferenced database states as well as the rows that constitute those unreferenced states. This will often improve performance.
You should compress periodically throughout the lifetime of the database. Keep in mind, the database can only be compressed when no one is using it. This is because the session that attempts to perform a compress operation must first acquire an exclusive lock on all states, and to acquire this lock no other sessions can be connected; if it cannot acquire this lock, it will fail immediately.
To ArcGIS 8.1
1. To get the most out of the compress operation, some preparation is helpful. For each outstanding version, reconcile against the DEFAULT version.
It is not necessary to post to the default version or delete all outstanding versions.
2. Connect to the database as the user 'SDE' and run the compress operation from ArcCatalog.
3. After compression is completed, recreate versions if necessary.
4. You should also update the statistics on your database using either 'sdetable -o update_dbms_stats' or SQL (for Oracle). You can do this in ArcCatalog for ArcGIS 8.1 with the new Analyze command:
ANALYZE <table> COMPUTE STATISTICS
It is recommended you periodically run Analysis on the database, especially after you make large changes to its contents.
ArcGIS 8.2 and later
Compress is now documented in online Help. Please refer to Creating and administering versions in ArcCatalog.