Partition Primary Index

General
Highlighted
Enthusiast

Partition Primary Index

Is the below usage Partition Primary Index correct ? Can you please clarify ?

 

PRIMARY INDEX A(B,C)

PARTITION BY RANGE_N(B BETWEEN 1 AND 65000 EACH 1,

NO RANGE,UNKNOWN);

 

 


Accepted Solutions
Senior Apprentice

Re: Partition Primary Index

Hi,

 

Depends what you mean by 'correct'.

 

Is it valid syntax? Yes.

Is it a very common PPI definition? Probably not.

 

Most PPI definitions will include a DATE column (most, but not all and they don't have to).

Most PPI definitions will not include a column from the PI.

 

When choosing your PPI columns you need to think mainly about which columns are most frequently used for selection and/or joining.

 

You don't have to worry (directly) about having a number from 1 to 65000 (or whatever) as the dbms will work out which partition(s) to use based on your query and the definition. For example you might have the following definition (which may not be the correct syntax):

PARTITION BY RANGE_N(sales_date BETWEEN DATE '2010-01-01' AND DATE '2020-12-31' EACH '1' DAY,NO RANGE, UNKNOWN)

With this definition in place, a query which includes the following:

WHERE sales_date BETWEEN DATE '2010-01-03' AND DATE '2010-01-06'

will use partitions 3 to 6.

 

See (https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1144_151K/partiti...) for more details.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: Partition Primary Index

Hi,

 

Depends what you mean by 'correct'.

 

Is it valid syntax? Yes.

Is it a very common PPI definition? Probably not.

 

Most PPI definitions will include a DATE column (most, but not all and they don't have to).

Most PPI definitions will not include a column from the PI.

 

When choosing your PPI columns you need to think mainly about which columns are most frequently used for selection and/or joining.

 

You don't have to worry (directly) about having a number from 1 to 65000 (or whatever) as the dbms will work out which partition(s) to use based on your query and the definition. For example you might have the following definition (which may not be the correct syntax):

PARTITION BY RANGE_N(sales_date BETWEEN DATE '2010-01-01' AND DATE '2020-12-31' EACH '1' DAY,NO RANGE, UNKNOWN)

With this definition in place, a query which includes the following:

WHERE sales_date BETWEEN DATE '2010-01-03' AND DATE '2010-01-06'

will use partitions 3 to 6.

 

See (https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1144_151K/partiti...) for more details.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Partition Primary Index

Thanks for your reply. I was wondering how can the range be that huge. I got the answer for that.