Archives of the TeradataForum

Message Posted: Wed, 05 Nov 2003 @ 13:09:13 GMT

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

All,

I'm being told that if any of the columns of an index include a null value, then the value for that row's index is considered null. This is causing problems for us. I've created a simplified example below.

Even though this table has 7 records, the optimizer (when building its join plan) only considers this table as having 1 unique record. I could add millions of additional records to this table (assuming each of the records include a null value), and the optimizer would still consider this table as having only 1 unique record. In our case, the optimizer is duplicating a similar table to all AMPs (because it thinks our table only has 1 unique record) eventhough the table actually contains 3.5 million records.

I think this is a bug. I'd like to get others people's input. Even thogh each of these rows hash to different location (see below), from a statistics point of view, Teradata considers all of these records as having the same unique value. This results in less than optimal join plans when this table is joined with other tables.

Any feedback would be greatly appreciated.

Thanks,

Clay

Create Table Test_Space.Null_Check
(
ColA Integer
,ColB INteger
,ColC Integer
)
Unique Primary Index (ColA, Colb, ColC);

Insert into Test_Space.Null_Check Values (null,1,1);
Insert into Test_Space.Null_Check Values (1,null,1);
Insert into Test_Space.Null_Check Values (1,1,null);
Insert into Test_Space.Null_Check Values (null,null,1);
Insert into Test_Space.Null_Check Values (1,null,null);
Insert into Test_Space.Null_Check Values (null,1,null);
Insert into Test_Space.Null_Check Values (null,null,null);

Collect Stats on Test_Space.Null_Check Index (ColA, ColB, ColC);

Help stats Test_Space.Null_Check;

Date      Time      Unique Values   Column Names
03/11/05  06:16:36              1   ColA,ColB,ColC

Select   HashRow(ColA,ColB,ColC)
From  Test_Space.Null_Check;

HASHROW(ColA,ColB,ColC)
79B69E37
2029536B
00000000
79B69E37
2029536B
79B69E37
2029536B

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