SQL> @OTN_DROP_CREATE_INDEX_STATS
SQL> SET PAGES 500
SQL> SELECT * FROM V$VERSION;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi                
PL/SQL Release 10.2.0.2.0 - Production                                          
CORE	10.2.0.2.0	Production                                                      
TNS for HPUX: Version 10.2.0.2.0 - Production                                   
NLSRTL Version 10.2.0.2.0 - Production                                          

SQL> DROP TABLE T;
DROP TABLE T
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


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> INSERT INTO T (T1) SELECT DBMS_RANDOM.string('U',25) FROM DUAL CONNECT BY LEVEL <= 50;

50 rows created.

SQL> COMMIT;

Commit complete.

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            2      14720          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> INSERT INTO T (T1) SELECT DBMS_RANDOM.string('U',25) FROM DUAL CONNECT BY LEVEL <= 200;

200 rows created.

SQL> COMMIT;

Commit complete.

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            2      14720          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           250                       1                    

SQL> 
SQL> EXIT