How teradata is using the Composite primary index effectively...?

Database
l_k
N/A

How teradata is using the Composite primary index effectively...?

Hi,

I have a table which contains DATE_KEY,STORE_KEY,PRODUCT_KEY are the composite index ie., PRIMARY INDEX (DATE_KEY,STORE_KEY,PRODUCT_KEY).Here DATE_KEY is a
PPI.

How teradata will use the composite primary index effectively? how to collect
the stats for those composite columns and multi column stats. need to be collected
on those columns?

Let me know , if you have any other suggestion on this..

Lavakumar.
1 REPLY

Re: How teradata is using the Composite primary index effectively...?

Hi Lavakumar,

Teradata uses primary index concept irrespective of number of columns in a same way i.e. to distribute the rows;

you can collect stats both at index level or column level if required.

while defining the primary index give an alias
PRIMARY INDEX comp_ind (DATA_KEY, STORE_KEY, PRODUCT_KEY)
then uses the following statement to collect the stats

COLLECT STATISTICS ON tablename INDEX comp_ind
;
Alias is not necessary, but it makes job simple with composite keys

or even you can collect on individual or combination of columns as follows:
COLLECT STATISTICS ON tablename COLUMN DATE_KEY;

COLLECT STATISTICS ON tablename COLUMN(DATE_KEY,STORE_KEY)
;

You can check the statistics collected by using the statement
HELP STATISTICS ON tablename;
which will displays all stats collected on table

Hope this helps you

bye
tc