dump database Performance

Due to the design of indexes within a dataserver that provides an optimum search path, index rows are ordered for fast access to the table’s data row. Index rows that contain row identifiers (RIDs), are treated as binary to achieve fast access to the user table.

Within the same architecture platform, the order of index rows remains valid, and search order for a selection criteria takes its normal path. However, when index rows are translated across different architectures, the order by which optimization was performed is invalidated, leading to an invalid index on user tables in a cross-platform dump and load.

When a database dump from a different architecture, such as big endian to little endian, is loaded, certain indexes are marked as suspect:
  • Nonclustered index on APL tables.

  • Clustered index on DOL tables.

  • Nonclustered index on DOL tables.

To fix indexes on the target system, after loading from a different architecture dump, either:
  • Drop and re-create all of the indexes, or,

  • Use sp_post_xpload. See System Procedures in Reference Manual: Procedures.

Re-creating indexes on large tables can be a lengthy process. sp_post_xpload validates indexes, drops invalid indexes, and re-creates dropped indexes, in a single command.

Using sp_post_xpload may take longer than dropping and re-creating indexes individually. You should use the drop and re-create indexes on those databases larger than 10GB.