Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Collect Stats on PI: Index vs Column
 
From:   Christie, Jon

  The only difference I have ever heard between collecting on columns vs index, is that if you drop the index - the stats go too.  


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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023