Archives of the TeradataForum
Message Posted: Wed, 05 Nov 2003 @ 13:09:13 GMT
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.
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|