Archives of the TeradataForum

Message Posted: Mon, 10 Dec 2007 @ 23:30:35 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The stats go away if the index is a multi-column index. If it's a single-column index, the stats remain. Why? Because single-column stats are kept in DBC.TVFields and multi-column stats are kept in DBC.Indexes. DROP INDEX blows away the DBC.Indexes row. DROP INDEX does not update DBC.TVFields to wipe out the stats for a single-column

Check this out by running explains.

     explain drop index (y) on jlc.foo;

       *** Help information returned. 20 rows.
       *** Total elapsed time was 1 second.

     Explanation
     ------------------------------------------------------------------------
        1) First, we lock a distinct jlc."pseudo table" for exclusive use on
           a RowHash to prevent global deadlock for jlc.foo.
        2) Next, we lock jlc.foo for exclusive use.
        3) We lock DBC.TVM for write on a RowHash, and we lock DBC.Indexes
           for write on a RowHash.
        4) We execute the following steps in parallel.
             1) We do a single-AMP ABORT test from DBC.DBase by way of the
                unique primary index.
             2) We do a single-AMP ABORT test from DBC.TVM by way of the
                unique primary index.
             3) We do a single-AMP DELETE from DBC.Indexes by way of the
                primary index.
             4) We do a single-AMP UPDATE from DBC.TVM by way of the unique
                primary index with no residual conditions.
        5) We drop the index subtable on jlc.foo.
        6) We update table jlc.foo 's version number.
        7) We spoil the parser's dictionary cache for the table.
        8) Finally, we send out an END TRANSACTION step to all AMPs involved
           in processing the request.
        -> No rows are returned to the user as the result of statement 1.

	<Prev	Next>		<<First	<Prev	Next>	Last>>