Archives of the TeradataForum

Message Posted: Mon, 26 Aug 2002 @ 12:12:02 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

thank you for your advice. It helps me a lot, though my problem has not been solved yet.

Following your advice, I checked the detailed stats infomation of my table.

'help statistics mytable' said like:

 Column Names: col2
 StatTime    : 02/08/21 15:30:15
 Version     : 1
 NumNulls    : 0
 NumIntervals: 73
 Numeric     : FALSE
 Min. Value  : '!       '
 Mode Value  : 'DDDDDDDD'
 Mode Freq   : 375946
 Total Values: 143
 Total Rows  : 694209

(snip)

 Interval[57]
   Max. Value: 'BBBBBBBA'
   Mode Value: 'BBBBBBAA'
   Mode Freq : 45
   Values    : 2
   Rows      : 4
 Interval[58]
   Max. Value: 'BBBBBBBC'
   Mode Value: 'BBBBBBBC'
   Mode Freq : 6
   Values    : 1
   Rows      : 1

(snip)

As my understanding, this means:

* Interval 58 contains 2values between 'BBBBBBBA' and 'BBBBBBBC'.

* 'BBBBBBBC' is the most frequent value in this interval and it occurs in 6rows.

* There is 1 value other than 'BBBBBBBC' in the interval and it occurs in 1 row.

So, now if a value 'BBBBBBBB' exists in my table, this should be that 1 other value in Interval 58 and it occurs in 1 row.

As far as I learned from 'Teradata RDBMS SQL reference Vol.2', the optimizer should be able to guess that row# with 'BBBBBBBBB' is 1 (= 1 row/1value) in such case.

But somehow my optimizer still insists to say 1, it says 4,855 (= total rows / total values)...

How can this happen?

	<Prev	Next>		<<First	<Prev	Next>	Last>>