I have tried creating a NUPI and a USI on the same column. It worked. Here is the DDL .
CREATE TABLE try5
PRIMARY INDEX (a);
CREATE UNIQUE INDEX ind_a(a) ON try5;
show table try5;
CREATE SET TABLE TD_8007_ECUSTDB.try5 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
b VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
c DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX ( a )
UNIQUE INDEX ind_a ( a );
select * from try5 where a=2;
Explain select * from try5 where a=2
1) First, we do a two-AMP RETRIEVE step from TD_8007_ECUSTDB.try5 by
way of unique index # 4 "TD_8007_ECUSTDB.try5.a = 2" with no
residual conditions. The estimated time for this step is 0.00
-> The row is sent directly back to the user as the result of
statement 1. The total estimated time is 0.00 seconds.
Now my question is ..why did the Teradata optimizer go for a two amp operation (USI) instead of using the NUPI which would have been a single AMP.
I am using Teradata 14 version.
Just a guess but maybe a NUPI on a set table with the possibility of duplicate row checks? Of course the USI would cover that. Maybe both are so fast it was a toss up! Wish I had this problem! ha