Create a PK and PI on a NoPI table

Database
N/A

Create a PK and PI on a NoPI table

Hello Teradata experts,

I have a question regarding NoPI tables.

We have a table, which should have a primary key, which also should be set as a unique primary index.

For performance reasons, I create this table as NoPI first (to run executeBatch faster), than I load data.

After that, I want to create a PK (with a corresponding PI) for this table.

I run the following SQL command:

ALTER TABLE <table_name> ADD PRIMARY KEY (<PK columns>);

Will this <PK columns> also be set as PI?

In teradata express studio in the "Data source explorer", I see that this columns are set as <anonymous> [UNIQUE PRIMARY] index.

However, I still see the "NO PRIMARY INDEX" constraint in the "create table" SQL in the Obect Viewer after I create the PK:

CREATE MULTISET TABLE dbentimice.<table_name> (<PK columns>, <other columns>, PRIMARY KEY ( <PK columns> )) NO PRIMARY INDEX                                           

Thank you!

Katja

4 REPLIES

Re: Create a PK and PI on a NoPI table

Katja:

When you create a NoPI table... well, it cannot have a Primary Index.

If you create a primary key on it, Teradata will enforce a USI instead. It STILL cannot create a PI on a NoPI table ;-)

BTW: NoPI Tables are always MULTISET tables.

HTH.

Cheers.

Carlos.

Teradata Employee

Re: Create a PK and PI on a NoPI table

You can use two tables.

You can load data into a NoPI staging table, and then do an INSERT...SELECT from the NoPI staging table into your PI table.

N/A

Re: Create a PK and PI on a NoPI table

Thank you for your help!

Carlos, okay, got it. I was confused by the Teradata Studio Express, which actually shows these columns as UPI after I've created a PK on a NoPI table, but I believe you more than Teradata Studio Express ;)

Tom, thanks, that's a good option.

I was loading a dataset (c.a. 1.2 Gb), using our application (connecting with jdbc, executing executeBatch): firstly, into a table with PK (in took couple of hours) and then the same dataset into multiset NoPI table (it took about 10 min). I was expecting some difference in performance, because of constraints checks, but this one was really dramatic.. Is it actually normal to have such a huge difference in performance between loading into a table with a PK and into a NoPI table?

Teradata Employee

Re: Create a PK and PI on a NoPI table

>>> Is it actually normal to have such a huge difference in performance between loading into a table with a PK and into a NoPI table?

That is the largest performance discrepancy that I have heard of for loading into a NoPI versus a PI table. Whether such a difference is expected or not depends on many factors, such as the constraints that you mentioned.

If you want to determine the reason(s) for the performance differences, you can use a process of elimination. Remove one constraint at a time, repeat the load, and measure the time.