Create NUPI and USI having the same Column

Database
Teradata Employee

Create NUPI and USI having the same Column

Hi

Under which circumstances does it make sense to define NUPI and USI having the same column?

It sounds curious, isn't it?

Thanks.

6 REPLIES
Teradata Employee

Re: Create NUPI and USI having the same Column

Is this a question or a quiz?  ;)

Only thing I can think of is on a partitioned table where the partitioning column is not part of the PPI and therefore the PI cannot be unique.

Enthusiast

Re: Create NUPI and USI having the same Column

I dont think its necessary if you do the data modeling right. 

Teradata Employee

Re: Create NUPI and USI having the same Column

It's a question. The table has no PPI.

Enthusiast

Re: Create NUPI and USI having the same Column

Hi..

Myself to have same question.. I have table with order id, date, amount and table going to hold billion of record for each day, where we need to maintain only 3 year of date.

table going to be MLPPI

Please help me, which combination helps to have better data access, join, aggregation etc

UPI with PPI or PI with PPI and USI? provided the below table defination for more details

Create set table orderID, no fallback,

..

..

(order_id int,

date1 date 'format dd/mm/yyyy',

amount decimal(12,2)

)

Unique primary index(order_id, date1)

partition by RANGE_N(date1 between date '2013/01/01' and '2016/01/31' each interval '1' Month);

---------------------------------------------------------------------------------------------------------------

create set table orderId, no fallback,

---

---

(

order_id int,

date1 date format 'dd/mm/yyyy',

amount decimal(12,2)

)

Primary index(order_ID)

partition by RANGE_N(date1 between '2013/01/01' and '2016/01/31' each INTERVAL '1' month)

UNIQUE INDEX( Order_ID, date1);

I believe having USI is overhead cost and maintainance. Please help me know PPI having USI or PPI having UPI will give better performance in terms of MLPPI.

Enthusiast

Re: Create NUPI and USI having the same Column

Well for starters, you should be building a multiset table, especially if you're going to use a unique index.  Using a NUPI with a USI is a pretty standard construct, as the NUPI is really a disk pointer to the row.

Your data access and aggregation performance are dependent upon the queries being run, and how the table is used/joined.  

If you're using the first option, composite PI, you'll need to use both of the columns ALL of the time in order to get PI access.  If your primary access column is order_id and you need to rely on TD to maintain uniqueness, the second option is going to be better, again it all depends on the queries that are being written against the object.

The USI ddl will cause an index subtable to be created, chewing up more disk space.  Any access other than PI access on the NUPI & USI ddl via the USI will be at a minimum, a two amp query. 

Cheers

Enthusiast

Re: Create NUPI and USI having the same Column

Many Thanks VandebergB

Regarding 1st option - Multiset table will be effective when during DML activity. i.e., It will ignore the uniqueness checking while doing insert, update, etc.

We will be using Composite PI every time i,e., table will be joined and aggredated using both OrderID and date1. In this scenario we can go with 1st Option. So that we can prevent disk space and acess of f data via single amp rather than going with two amp if we use USI.

Please let me know you thoughts.

Regarding 2nd option - If the business requirment changed that UNPI(order id) will be used frequently to check the balance details using OLAP functions by joining various table to get balance details.

In this Case 2nd option is fine?

In general its good Standard to construct a PPI table with Secondary Index ? Please show some lights .

Thanks in advance