what is the difference between Partitioned primary index and secondary index

Tools & Utilities

what is the difference between Partitioned primary index and secondary index

what is the difference between Partitioned primary index and secondary index

please tell me as early as possible

thanks & regards
k.srinivasarao
4 REPLIES
Enthusiast

Re: what is the difference between Partitioned primary index and secondary index

Partitioned primary index is basically physically splitting the table into a series of subtables, one for every partitioning value. Data for the partitioning values is held together on the DBC.
When a single row is accessed, it looks first at the partitioning value to determine the subtable, then at the primary index to calculate the rowhash for the row(s). The partitioning expression may or may not be a component of the primary index.

It is usually used to split data into time-periods. It you need to access all the records for a month/ year, and the table has a PPI based on the month/ year, then ths DBC will only scan the subtables for the periods required - so you only scan a fraction of the data.

A secondary index is a subtable which contains only the secondary index values, with a pointer to the data row. So if you have a SI based on the month/ year, it will go through the SI, get the pointers and then go off to the data rows. (Or, if there are not too many months data there, it will decide not to bother and scan the table!)

PPI's should be at a very high level, with lots of records with the same partitioning value. SI's should be the reverse - one or a few SI values.

Hope this helps.

Re: what is the difference between Partitioned primary index and secondary index

Please elaborate ur explanation i m not able to understand the logic properly

Re: what is the difference between Partitioned primary index and secondary index

@Rohan, You can send a mail to y.deepaksatiec@gmail.com with your queries if you face any issues in teradata.

Enthusiast

Re: what is the difference between Partitioned primary index and secondary index

in simple ppi is used to store and retrive the data in partition wise

exampel i want to retirve data of a particular  department . in where clause i will mention partition column deptno . so that data will be retrived very fastly because particular dept. data will be stored in one partition in amps.

select *from emp where deptno=20;

secondary index are an alternative path for accessing the data. comparetively full table scan it is faster. it is used in some cases like other than primary index in our where clause frequently using. like by last_name column wise or by area wise

select *from emp where emp_loc='new york';