Under which circumstances does it make sense to define NUPI and USI having the same column?
It sounds curious, isn't it?
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.
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,
date1 date 'format dd/mm/yyyy',
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,
date1 date format 'dd/mm/yyyy',
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.
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.
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