|
|
Archives of the TeradataForum
Message Posted: Mon, 10 Dec 2007 @ 23:30:35 GMT
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.
| |