All Forums Database
k3na 19 posts Joined 09/14
22 Jun 2015
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!

CarlosAL 512 posts Joined 04/08
22 Jun 2015

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.

tomnolan 594 posts Joined 01/08
22 Jun 2015

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.

k3na 19 posts Joined 09/14
23 Jun 2015

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?

tomnolan 594 posts Joined 01/08
23 Jun 2015

>>> 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.

You must sign in to leave a comment.