Multi Column Primary Index and Secondary Index

General
Enthusiast

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)

7 REPLIES
Teradata Employee

Re: Multi Column Primary Index and Secondary Index

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?

Enthusiast

Re: Multi Column Primary Index and Secondary Index

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.

Teradata Employee

Re: Multi Column Primary Index and Secondary Index

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.

Enthusiast

Re: Multi Column Primary Index and Secondary Index

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

Enthusiast

Re: Multi Column Primary Index and Secondary Index

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

Teradata Employee

Re: Multi Column Primary Index and Secondary Index

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.

Enthusiast

Re: Multi Column Primary Index and Secondary Index

Many Thank Fred. Helps lot!