學習VALIDATE STRUCTURE

tolywang發表於2010-09-20

VALIDATE STRUCTURE

Specify VALIDATE STRUCTURE to validate the structure of the analyzed object. The statistics collected by this clause are not used by the Oracle Database optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses.


See Also:


  • For a table, Oracle Database verifies the integrity of each of the data blocks and rows. For an index-organized table, the database also generates compression statistics (optimal prefix compression count) for the primary key index on the table.

  • For a cluster, Oracle Database automatically validates the structure of the cluster tables.

  • For a partitioned table, Oracle Database also verifies that each row belongs to the correct partition. If a row does not collate correctly, then its rowid is inserted into the INVALID_ROWS table.

  • For a temporary table, Oracle Database validates the structure of the table and its indexes during the current session.

  • For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform. these operations by validating the structure of the table with the clause.

    Oracle Database also computes compression statistics (optimal prefix compression count) for all normal indexes.

    Oracle Database stores statistics about the index in the data dictionary views INDEX_STATS and INDEX_HISTOGRAM.


    See Also:

    for information on these views

If Oracle Database encounters corruption in the structure of the object, then an error message is returned to you. In this case, drop and re-create the object.

=============================================================================  

例子:  


SQL> analyze  index   dfms.IDX1_WIP_D_WO_DETAIL_COMID  validate structure ;

select name, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100)
"Frag Percent"  from index_stats   where   name = 'IDX1_WIP_D_WO_DETAIL_COMID'  ;


SQL> select name, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100)
  2   "Frag Percent"  from index_stats   where   name = 'IDX1_WIP_D_WO_DETAIL_COMID'  ;


NAME                                                 DEL_LF_ROWS    LF_ROWS    Frag Percent
------------------------------ ----------- ---------- ------------
IDX1_WIP_D_WO_DETAIL_COMID         1732765    1817931           95 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-674476/,如需轉載,請註明出處,否則將追究法律責任。

相關文章