4 Replies Latest reply on Jul 19, 2006 6:56 PM by 500324

    DROP and CREATE INDEX and need for ANALYZE INDEX ... ESTIMATE STATISTICS

    390534
      In a large migration process one of my steps is to drop and rebuild indexes. Do I still need to ALSO do a ANALYZE INDEX to make sure the index is up to date with data and good to go with the best performance possible? Doesn't drop and recreating force it to be most recent and I would basically be doing same work twice?

      This is Oracle 8i I am running on.

      Just curious,
      Miller
        • 1. Re: DROP and CREATE INDEX and need for ANALYZE INDEX ... ESTIMATE STATISTICS
          500324
          Analyze doesn't change the data in the index. Among other things it will simply populate the statistics so that the optimizer has some good information to work with when the table is queried.

          I am not sure if it works in 8i, but it might, when you create an index you can tell the index to get statistics at build time.

          create index idx on t(column) compute statistics;
          • 2. Re: DROP and CREATE INDEX and need for ANALYZE INDEX ... ESTIMATE STATISTICS
            390534
            Sorry wrong choice in my description...

            But point being.. Doesn't the equivalent of COMPUTE STATISTICS occur in a DROP and CREATE statment??????


            Miller
            • 3. Re: DROP and CREATE INDEX and need for ANALYZE INDEX ... ESTIMATE STATISTICS
              500324
              In 10xe it does - which is news to me. Sorry I don't have access to any other version at the time. If others don't answer you will have to test it.
              I tested it with this.

              DROP TABLE T;
              CREATE TABLE t (t1 VARCHAR2(30));
              CREATE INDEX idx ON T(t1);

              SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';
              SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';

              INSERT INTO T (T1) SELECT DBMS_RANDOM.string('U',25) FROM DUAL CONNECT BY LEVEL <= 50;
              COMMIT;


              SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';
              SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';

              ANALYZE TABLE T COMPUTE STATISTICS;

              SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';
              SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';

              DROP INDEX IDX;
              --added more rows while no index to make sure the stats are new and not "remembered"
              INSERT INTO T (T1) SELECT DBMS_RANDOM.string('U',25) FROM DUAL CONNECT BY LEVEL <= 200;
              COMMIT;

              CREATE INDEX idx ON T(t1);

              SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';
              SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';
              • 4. Re: DROP and CREATE INDEX and need for ANALYZE INDEX ... ESTIMATE STATISTICS
                500324
                The stats are NOT recreated in oracle 8i in my environment, but it does on 9 and 10. I connected to work (on holidays) and tested it.