Tuesday, 25 December 2012

ANALYZE statement with the VALIDATE STRUCTURE option


To verify structure of a table, index, materialized view, use the ANALYZE statement with the VALIDATE STRUCTURE option. If the structure is valid, no error is returned. However, if the structure is corrupt, you receive an error message.

For example, in rare cases such as hardware or other system failures, an index can become corrupted and not perform correctly. If index is corrupt, you can drop and re-create it.

If a table, index is corrupt, you should drop it and re-create it. If a materialized view is corrupt, perform a complete refresh and ensure that you have remedied the problem. If the problem is not corrected, drop and re-create the materialized view.

The following statement analyzes the table:

ANALYZE TABLE <table_name> VALIDATE STRUCTURE;
You can validate an object and all dependent objects (for example, indexes) by including the CASCADE option. The following statement validates the table and all associated indexes:
ANALYZE TABLE <table_name>  VALIDATE STRUCTURE CASCADE;
By default the CASCADE option performs a complete validation. Because this operation can be resource intensive, you can perform a faster version of the validation by using the FAST clause. This version checks for the existence of corruptions using an optimized check algorithm, but does not report details about the corruption. If the FAST check finds a corruption, you can then use the CASCADE option without the FAST clause to locate it. The following statement performs a fast validation on the table and all associated indexes:
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE FAST;
You can specify that you want to perform structure validation online while DML is occurring against the object being validated. There can be a slight performance impact when validating with ongoing DML affecting the object, but this is offset by the flexibility of being able to perform ANALYZE online. The following statement validates the table and all associated indexes online:
ANALYZE TABLE <table_name>  VALIDATE STRUCTURE CASCADE ONLINE;

No comments:

Post a Comment