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;
Comments
Post a Comment