Distribution method of tables specified with primary key only

Database
Teradata Employee

Distribution method of tables specified with primary key only

Would you please give me some advice for about using the following 'create statement' to create table, which item value will data be distributed?
Whether considered to be UPI specified in PK designation items?

create table tableA ( col1 char(1), col2 char(1) ,primary key (col2));

Thanks & Best regards

David


Accepted Solutions
Senior Apprentice

Re: Distribution method of tables specified with primary key only

Hi David,

 

In your original post you asked "which item value will data be distributed?"

 

You're correct that Teradata distributes data across AMPs in the configuration. This distribution is controlled by the Primary Index (PI) of the table.

 

The sample 'create table' statement that you gave was:

create table tableA ( col1 char(1), col2 char(1) ,primary key (col2));

 

When you send a 'ceate table' statement to the Teradata database it chooses the PI of the table using the following sequence of tests. As soon as a test is determined to be true no others are checked.

1) If you code PRIMARY INDEX or NO PRIMARY INDEX then that clause is used.

2) If you code PRIMARY KEY then that clause is used. <<< this is your situation

3) If you code a UNIQUE constraint then that clause is used

4) Look in the DBSControl record (Teradata's system-wide parameter record) for the "PrimaryIndexDefault" setting which will cause one of two things to happen:

- the first column in your table definition is chosen as a Non-Unique Primary Index (NUPI), or

- the table will be created as a No Primary Index table (NOPI).

 

Does that help?

 

Dave

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

Re: Distribution method of tables specified with primary key only

Hi David,

Teradata will use 'col2' and implement it as a Unique Primary Index.

Cheers,

Dave

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

Re: Distribution method of tables specified with primary key only

Hello dear Dave,

This is David, thank you very much for your kindly reply for my 2 message. 

For this message reply, i really dont know what is the meaning for your mentioned.

Would you please give me much more detail for this question?

thanks in advance.

best regards

David

Senior Apprentice

Re: Distribution method of tables specified with primary key only

Hi David,

 

In your original post you asked "which item value will data be distributed?"

 

You're correct that Teradata distributes data across AMPs in the configuration. This distribution is controlled by the Primary Index (PI) of the table.

 

The sample 'create table' statement that you gave was:

create table tableA ( col1 char(1), col2 char(1) ,primary key (col2));

 

When you send a 'ceate table' statement to the Teradata database it chooses the PI of the table using the following sequence of tests. As soon as a test is determined to be true no others are checked.

1) If you code PRIMARY INDEX or NO PRIMARY INDEX then that clause is used.

2) If you code PRIMARY KEY then that clause is used. <<< this is your situation

3) If you code a UNIQUE constraint then that clause is used

4) Look in the DBSControl record (Teradata's system-wide parameter record) for the "PrimaryIndexDefault" setting which will cause one of two things to happen:

- the first column in your table definition is chosen as a Non-Unique Primary Index (NUPI), or

- the table will be created as a No Primary Index table (NOPI).

 

Does that help?

 

Dave

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

Re: Distribution method of tables specified with primary key only

Hello Dave,

Really thank you so much for your kindly and detail information .

Yes, i think i understand this .

Thank you very much

Best regards

David