I have tried creating a NUPI and a USI on the same column. It worked. Here is the DDL .
CREATE TABLE try5 ( a INTEGER, b VARCHAR(20), c DATE ) 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, DEFAULT MERGEBLOCKRATIO ( a INTEGER, 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 seconds. -> 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.