Statistics on STJIs vs Base Columns

Database

Statistics on STJIs vs Base Columns

Hi. I've been reading this forum for some time now but it's the first time I'm posting anything. I have a question regarding the collection of statistics when it comes to Join Indexes. I've read in a topic on teradata.com ("Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns") that "it's generally best not to collect statistics on the index columns and instead to collect them on the corresponding columns of the base tables. This optimizes both system performance and disk storage by eliminating the need to collect the same data reduntantly."

I have created STJIs for several tables for the needs of a View (different join conditions, therefore different primary indexes on the STJIs). However, when I use the EXPLAIN on my view (with DIAGNOSTICS HELPSTATS ON FOR SESSION), I get stats recommendations on all my STJIs for "PARTITION" and "PARTITION, PI1, PI2, etc" (the base tables are usually PARTITIONED by a DATE column so I've also partitioned my STJIs). These recommended stats are collected (exist) on the base table, though.

So my question is whether I need to collect stats on the STJIs or not.

 

kyris

5 REPLIES

Re: Statistics on STJIs vs Base Columns

anyone can help on this?

Re: Statistics on STJIs vs Base Columns

I understand that I need to be patient to get an answer but it's been like a month ago! Did I do anything wrong regarding following the guidelines for new posts? Was my question not clear enough

Can nobody help me on this?

Teradata Employee

Re: Statistics on STJIs vs Base Columns

PARTITION is not actually a base table column. Even if the partitioning expressions happen to match between the base table and a single-table non-aggregate JI, stats for the PARTITION "column" in the base table are not used in place of PARTITION "column" stats on the JI itself.

Re: Statistics on STJIs vs Base Columns

Hi Fred and thanks for replying. To answer my question, I DON'T need to collect statistics on the recommended columns displayed using EXPLAIN? Correct?

Teradata Employee

Re: Statistics on STJIs vs Base Columns

You SHOULD collect PARTITION stats on the JI if it has a PPI. Whether you need to collect all the "recommended" stats for the JI is a separate question, just as it is for the base table.