All Forums General
Smac 6 posts Joined 07/16
03 Aug 2016
Multi Column Primary Index and Secondary Index

Hi All,
My 1st post in this Forum. Could you please help me to define a table if table having index as mulit column.
acctid and bus date are going to be unique values in the table. i.e., Multi column Index. Creating table with unique index ( acctid, Bus_date) or  unique Primary index ( acctid, Bus_date) is good in terms of data retrieval?
I believe unique secondary index is 2 AMP operation and Unique primary index is 1 AMP operation.  
CREATE SET TABLE table1,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     ( acctid int, bus_date date format 'dd.mm.yyyy', cust_name, cust_DOB, Bal) Primary Index( acctid) unique index ( acctid, Bus_date)
 
CREATE SET TABLE table2,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     ( acctid int, bus_date date format 'dd.mm.yyyy', cust_name, cust_DOB, Bal)  unique Primary index ( acctid, Bus_date)
 
 

Fred 1096 posts Joined 08/04
04 Aug 2016

How often will you retrieve a specific (acctid, Bus_date) combination?
How often will you retrieve data for an acctid but not know the specific Bus_date?
 

Smac 6 posts Joined 07/16
05 Aug 2016

Thanks Fred.
Basically its an snapshot table going to hold daily date. We will retrive the data on daily basis based on date and used to join multiple summary tables to generate report.
 
 

Fred 1096 posts Joined 08/04
05 Aug 2016

So with either PI, you will scan the table. If you partition on BUS_DATE then you don't have to scan the entire table; you can do that with either PI.
 
If you need the database to enforce uniqueness, then UPI will do so without additional overhead. NUSI + USI will take more space and overhead on insert/delete.

Smac 6 posts Joined 07/16
07 Aug 2016

Thanks Fred.
Now our requirement changes now. It seems acctid will also be used for joining and analysis purpose.
Basically we need to use Accid alone for joining in some scenarios and Accid,bus date combination joining to get data based on processing date in other scenarios.
sel a.*, b.* from
table1 a -- snap shot table, which we are discussing about index
inner join
table2 b -- summary table
on a.accit = b.accit
sel * from
table1 a -- snap shot table
inner join
table2 b - another snap shot table
on a.acctid - b.acctid
and a.bus date = b.bus date
where a.bus date = date ( date will be passed thru macro)
 
For above tow scenarios we go with UPI( acctid, bus date) combination or PI(acctid) USI(acctid, bus date) for better retrieval of data?
please suggest your thoughts and suggestion :-)
thanks in Advance
 
 

yuvaevergreen 93 posts Joined 07/09
11 Aug 2016

IF its a big table, definitely partition will help....

Fred 1096 posts Joined 08/04
15 Aug 2016

Non-unique PI(acctid) may be best choice if number of rows per acctid isn't too huge.
Partitioning on bus_dt may be better choice than USI. Again, somewhat depends on avg / max number of rows with same PI.

Smac 6 posts Joined 07/16
15 Aug 2016

Many Thank Fred. Helps lot!
 

You must sign in to leave a comment.