1 Reply Latest reply: May 11, 2011 6:45 AM by P.Forstmann RSS

    ANALYZE TABLE .... VALIDATE STRUCTURE

    ma365
      Hi,

      I'm currently investigating the following commands so that we can check objects for corruption.

      ANLAYZE TABLE xxxxx VALIDATE STRUCTURE;
      ANLAYZE TABLE xxxxx VALIDATE STRUCTURE ONLINE;
      ANLAYZE TABLE xxxxx VALIDATE STRUCTURE CASCADE;
      ANLAYZE TABLE xxxxx VALIDATE STRUCTURE CASCADE ONLINE;

      We're only interested in the validation function of the analyze command as we already use DBMS_STATS to gather object statistics.

      Database version is 10.2.0.5 on Solaris 10.

      Reading the documentation throws up a couple of questions.

      1. What affect does the ANLAYZE TABLE xxxxx VALIDATE STRUCTURE command have on the existing statistics?

      My current understanding is that these commands do not touch the optimizer statistics. Is this correct?

      2. How does specifying the ONLINE clause reduce the validation performed?

      The ANALYZE entry in the 10g SQL Reference (Part Number B14200-02) states the following "Specify ONLINE to enable Oracle Database to run the validation while DML operations are ongoing within the object. The database reduces the amount of validation performed to allow for concurrency."

      Can any one help understand what the database omits to reduce the amount of validation when performed online? I can't find any other reference to this in the docs or MOS and would like to understand if the benefits of doing the validate online and undermined by the reduced amount of validation performed.

      Any input appreciated.
      Thanks in advance
        • 1. Re: ANALYZE TABLE .... VALIDATE STRUCTURE
          P.Forstmann
          ma365 wrote:

          1. What affect does the ANLAYZE TABLE xxxxx VALIDATE STRUCTURE command have on the existing statistics?

          My current understanding is that these commands do not touch the optimizer statistics. Is this correct?
          Yes, this is what SQL Reference doc.says http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4005.htm#i2086320:

          >
          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.