All Forums Database
ka250107 4 posts Joined 11/15
30 Nov 2015
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
 

ka250107 4 posts Joined 11/15
06 Dec 2015

anyone can help on this?

ka250107 4 posts Joined 11/15
21 Dec 2015

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?

Fred 1096 posts Joined 08/04
21 Dec 2015

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.

ka250107 4 posts Joined 11/15
21 Dec 2015

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?

Fred 1096 posts Joined 08/04
22 Dec 2015

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.

You must sign in to leave a comment.