NUPI and USI on the same column

Database
Enthusiast

NUPI and USI on the same column

Hi all,

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.

Thanks,

1 REPLY
Enthusiast

Re: NUPI and USI on the same column

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