9) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an all-rows scan, which is joined to Spool 12 (Last Use) by way of an all-rows scan. Spool 11 and Spool 12 are joined using a hash join of 3 partitions, with a join condition of ("(ACCOUNTING_DATE_KEY =CALENDAR_DATE) AND (ORGANIZATION_IDENTIFIER =ORGANIZATION_IDENTIFIER)"). The result goes into Spool 16(all_amps), which is built locally on the AMPs. The size of Spool 16 is estimated with no confidence to be 6,352,202 rows.The estimated time for this step is 2.19 seconds.
i have tracked all the previous steps i.e spool 11 and spool 12 and there previous steps also, those steps are showing HIGH Confindence. so even though stats are there on all the columns of the above join condition showned in explain plan, why i still getting no confidence.
there is a multicolumn stats on ACCOUNTING_DATE_KEY, ORGANIZATION_IDENTIFIER together and they are in where clause part and of the same table.
I hope we do not have stats for spool. The Join is now between Spools and the stats information is not carried to spool files. But, I remember that there is some concept called derived stats.Please find with some one whether this step fetches stats from derived stats.Otherwise, the confidence will be either Low or No confidence
Thanks for the i/p. may i know from where can i get the information regarding Derived stats. I checked it in all the teradata pdf, but i am unable to find the term called derived stats. can u tell me in which teradata pdf or site or...? i could get infor on this.