ASA Database Administration Guide
Backup and Data Recovery
Backup and recovery internals
The VALIDATE TABLE statement can be slow when used on large databases running on servers with a cache size too small to contain the table and its largest index. It is often the case that all pages in the table are read at least once for each index. As well, if full compares are required for index lookups, the number of page reads can be proportional to the number of rows (not pages) in the table.
If you want to reduce the time taken to validate, you can use the WITH EXPRESS CHECK option with the VALIDATE TABLE statement, or the -fx option with the dbvalid utility. Depending on the size of your database, the size of your cache, and the type of validation you require, these two features can significantly reduce the time taken to perform validation.
Express validation causes each row of the table to be read and all columns evaluated, as is done with traditional validation using the WITH DATA CHECK clause. Each index is completely scanned once, and checks are done to ensure that the rows referenced in the index exist in the table. The Express check option also does checks on the validity of individual index pages. The number of rows in the table must match the number of entries in the index. The Express option saves time because it does not perform individual index lookups for each row.
Because the Express check feature does not perform individual lookups, it is possible (though unlikely) for some form of index corruption to go unnoticed by the Express validation feature. If index corruption should occur, data can be recovered by unloading and rebuilding the database since validation has confirmed that all of the data can be read.
Express validation is only supported for databases created with ASA 8.0 or later.
For more information about the Express check option, see the VALIDATE TABLE statement, Validating a database using the dbvalid command-line utility, and the sa_validate system procedure.