We have a composite primary index column and partitioning is done on column whcih is not part of primary index , hence we are not in position to create unique primary index.
we thought of achiving this by two approach, one creating the USI but the problem is NUPI and USI are going to be same column and there will be overhead of creating subtable .
another way is to create primary key instead of creating USI. we will be inserting around 50 to 70 million records from stage to target once in a month and target table has around 1.5 billion records. whether it is adviceable to create primary key over USI for this scenario ?
Insert will be through direct select / insert instead of using teradata utilities.
I tested few scenarios... Created with non unique primary index on composite columns and table is partitioned on date column .. inserted 2 duplicate records to that table which has already billion of records and triggered USI creation script. but USI creation failed with error message *** Failure 2930 Last session request caused the DBS to crash. Statement# 1, Info =0 , but not sure why it failed with that error message, i was expecting another error message secondary index failed due to duplicate... Kindly help to clarify