SQL> @OTN_DROP_CREATE_INDEX_STATS_V8
SQL> SET ECHO ON
SQL> SET PAGES 500
SQL> SELECT * FROM V$VERSION;

BANNER                                                                          
----------------------------------------------------------------                
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production                
PL/SQL Release 8.1.7.4.0 - Production                                           
CORE	8.1.7.0.0	Production                                                       
TNS for HPUX: Version 8.1.7.4.0 - Production                                    
NLSRTL Version 3.4.1.0.0 - Production                                           

SQL> DROP TABLE T;

Table dropped.

SQL> CREATE TABLE t (t1 VARCHAR2(30));

Table created.

SQL> CREATE INDEX idx ON T(t1);

Index created.

SQL> 
SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT                        
---------- ---------- ------------ ---------- ----------                        
                                                                                

SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY                    
---------- ----------- ------------- -----------------------                    
                                                                                

SQL> 
SQL> BEGIN
  2  FOR X IN 1..50 LOOP
  3  INSERT INTO T (T1) SELECT DBMS_RANDOM.string('U',25) FROM DUAL;
  4  END LOOP;
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT                        
---------- ---------- ------------ ---------- ----------                        
                                                                                

SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY                    
---------- ----------- ------------- -----------------------                    
                                                                                

SQL> 
SQL> ANALYZE TABLE T COMPUTE STATISTICS;

Table analyzed.

SQL> 
SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT                        
---------- ---------- ------------ ---------- ----------                        
        50          1           62       6552          0                        

SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY                    
---------- ----------- ------------- -----------------------                    
         0           1            50                       1                    

SQL> 
SQL> DROP INDEX IDX;

Index dropped.

SQL> BEGIN
  2  FOR X IN 1..200 LOOP
  3  INSERT INTO T (T1) SELECT DBMS_RANDOM.string('U',25) FROM DUAL;
  4  END LOOP;
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> CREATE INDEX idx ON T(t1);

Index created.

SQL> 
SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME='T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT                        
---------- ---------- ------------ ---------- ----------                        
        50          1           62       6552          0                        

SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY FROM USER_INDEXES WHERE INDEX_NAME='IDX';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY                    
---------- ----------- ------------- -----------------------                    
                                                                                

SQL> 
SQL> EXIT