Statistics Collection Recommendations for Teradata 12

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Re: Statistics Collection Recommendations for Teradata 12

Hi Carie,
I have a Table Which is NPPI (PI (ColA) is Unique, but Partitoned columns (Col B and Col C) are not part of PI, Hence NPPI.
Based on your other Recommendations, Is my below understanding righht?
COLLECT STATS ON TABLE INDEX (Col A);
COLLECT STATS ON TABLE COLUMN PARTITION;
COLLECT STATS ON TABLE COLUMN ColB;
COLLECT STATS ON TABLE COLUMN ColC;
COLLECT STATS ON TABLE COLUMN (PARTITION, ColA, ColB);
COLLECT STATS ON TABLE COLUMN (PARTITION, ColA, ColC);

Thanks,
Toad
Fan

Re: Statistics Collection Recommendations for Teradata 12

Hi carrie,
can u explain the retriving of data from join index table and base table
when i create a join index how it is going to store in data distionary can u explain
can i use select statement on join index table

Thanks
Abdul
Teradata Employee

Re: Statistics Collection Recommendations for Teradata 12

Hi Abdul,

Data is retrieved from a join index the same as from a base table, either using primary index access, or using partition elimination (if PPI has been defined) or doing a table scan. Also, you can build a NUSI on a join index, so you could also have NUSI access as well.

Detail about the join index is held in these data dictionary tables: tvm, tvfields, and indexes. These are the same dictionary tables as used to store the definition of a base table (but with a different kind of code).

You cannot directly access a join index. It is always a decision made by the optimizer whether or not to access a join index.

You can read more about join indexes, how they work, and how to use them, in the Database Design Manual, accessible at: http://www.info.teradata.com
Teradata Employee

Re: Statistics Collection Recommendations for Teradata 12

Sorry, Toad, I totally missed your earlier comment.

Yes, that looks good in terms of stats to collect on, with one exception.

If this is a single-level PPI table and the partitioning colums include (ColB, ColC) as multicolumn set, then you want to collect stats on them as a multi-column stat. If they are part of different partitioning column definitions on different levels of a multi-level PPI table, then collect on them separately.

You may not actually need all those stats, but if you are able to collect them, it will cover a lot of different possibilities where stats could be useful with a PPI table.

Thanks, -Carrie
Enthusiast

Re: Statistics Collection Recommendations for Teradata 12

Hi Carrie,

I have a question concerning the case of partitioned primary index table where the partioning column is not part of the primary index. As you mentioned in point 3 and 4. The following stats should be collected:
. PARTITION
. partioning column
. (PARTITION, PI)
. (PARTITION, PI, partitioning column)

Is it neccesary to collect stats on PI if we already collect stats on (PARTITION, PI) ?

In general, if we need collect stats on another column (eg. column) on PPI case, should we collect stats only on (column) or (PARTITION, column) or both ? Logically, I think when we have already statistics info on (PARTITION, column), it means we already have stats info on this column so don't need anymore collect stats (column). Am I right ?

Not applicable

Re: Statistics Collection Recommendations for Teradata 12

Hi Carrie,

How can we find un-used statistics of a table ?

e.g. if i have one table tableA , on 5 columns statistics are collected i.e. on col1, col2, col3, col4 & col5 there are total 10 columns in a table
now when i execute any query on the column4 or column5 by specifying some criteria
my query is not using the statistics collected on those 2 columns like that way i would
like to know how many statistics are got collected but never use by any of the queries
please treat this on urgent basis, thanx in advance
Teradata Employee

Re: Statistics Collection Recommendations for Teradata 12

There is no way that I am aware of for determining which statistics are actually used by the optimizer when building a query plan. You have to use your best judgement on which statistics to collect. Once you collect statistics, if the table undergoes growth, you may need to recollect statistics on those same columns. Stale statistics may be worse than no statistics at all.

In general, the recommendations in the above article are the best suggestions we have to guide you in that process.

Thanks, -Carrie
Enthusiast

Re: Statistics Collection Recommendations for Teradata 12

Hi Carrie,
My question is about Stats collection after upgrade from TD12 to TD13.10.i have the stats collected on the tables(at columns,indexes,multicolumn) in td12.After upgrade to td13.10,do we only need to recollect stats at table level only ?
collect stats on databasename.tablename ;

or
i need to collect stats at column and index level as well..?
Teradata Employee

Re: Statistics Collection Recommendations for Teradata 12

Hello ilf.

You only need to re-collect statistics at the table level, if what you want is for all previously-collected statistics to be refreshed. When you recollect at the table level, all current stats for that table will be recollected, whether they are at the column or index level. The table level collection will also remember if you have collected statistics with the USING SAMPLE option. Basically, it looks at all the statistics histograms that exist for that table and rebuilds them all.

Thanks, -Carrie
Enthusiast

Re: Statistics Collection Recommendations for Teradata 12

thnx alot Carrie