All Forums Database
hyma 33 posts Joined 07/11
30 Jun 2016
Create NUPI and USI having the same Column

Under which circumstances does it make sense to define NUPI and USI having the same column?
It sounds curious, isn't it?

jkurdsjuk 6 posts Joined 01/14
30 Jun 2016

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.

Jake Kurdsjuk

hvganipineni 14 posts Joined 09/09
30 Jun 2016

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

Harsha Ganipineni

hyma 33 posts Joined 07/11
30 Jun 2016

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

Smac 6 posts Joined 07/16
19 Aug 2016

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.

VandeBergB 182 posts Joined 09/06
19 Aug 2016

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. 

Some drink from the fountain of knowledge, others just gargle.

Smac 6 posts Joined 07/16
22 Aug 2016

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

You must sign in to leave a comment.