SET TABLES WITH UPI AND NUPI - Help needed to check the behavior while loading data

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

SET TABLES WITH UPI AND NUPI - Help needed to check the behavior while loading data

Here are two tables. Table_3 and Table_4

 

Table_3 - SET with UPI

Table_4 - SET with NUPI

 

I am trying to find out the performance impact on SET table while loading data into to it.

 

If table is SET it will not allow duplicate rows. To achieve this while loading, complete table will be searched for duplicity and if there is none then it will allow loading the row.

 

So the question is, if table is SET with UPI (table_3) will it check complete table for duplicity or because of UPI it will skip this check and rely on UPI for uniqueness of rows

 

 

Code:

 

DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

 

 

CREATE SET TABLE table_3
(
col1 INTEGER,
col2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
col3 INTEGER NOT NULL,
col5 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
col6 INTEGER,
col7 INTEGER
)
UNIQUE PRIMARY INDEX ( col1 ,col5 );

INSERT INTO table_3
(col1,col2,col3,col5,col6,col7)
VALUES(3,'table_3',3,'table_3',3,3);

CREATE SET TABLE table_4
(
col1 INTEGER,
col2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
col3 INTEGER NOT NULL,
col5 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
col6 INTEGER,
col7 INTEGER
)
PRIMARY INDEX ( col1 ,col5 );

INSERT INTO table_4
(col1,col2,col3,col5,col6,col7)
VALUES(4,'table_4',4,'table_4',4,4);

4 REPLIES
Apprentice

Re: SET TABLES WITH UPI AND NUPI - Help needed to check the behavior while loading data

Hi,

"If table is SET it will not allow duplicate rows. To achieve this while loading, complete table will be searched for duplicity" - No.

It is not a 'complete table search', the dbms only checks rows with the same row hash.

Dave

 

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

Re: SET TABLES WITH UPI AND NUPI - Help needed to check the behavior while loading data

(sorry, hit 'reply' too soon...)

 

In this situation the processing will be essentially the same. Remember that the PI is not a separate structure (like an SI) and so whether it is checking a NUPI or UPI table it simply checks all rows with the same row hash. You might find a UPI check is slightly faster as in principle it only has to check the PI column(s).

Dave

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

Re: SET TABLES WITH UPI AND NUPI - Help needed to check the behavior while loading data

thanks for your reply Dave.

I have question for your comment

 

"If table is SET it will not allow duplicate rows. To achieve this while loading, complete table will be searched for duplicity" - No.

It is not a 'complete table search', the dbms only checks rows with the same row hash.

Even for checking on row hash on rows dbms will have to hash all the rows, that way it will touch each row while loading in SET table, right?

 

Highlighted
Apprentice

Re: SET TABLES WITH UPI AND NUPI - Help needed to check the behavior while loading data

Hi,

 

No. The dbms does not read all rows in the table.

 

(Ignoring PPI for now), rows are stored in rowhash order and the rowhash is included in the physical row.

 

Assume that you have the following table def'n:

create set table t1
(col1 integer
,col2 integer
,col3 integer)
unique primary index(col1);

Assume that this table contains the following rows. I've also shown (made up) row hash values and a column 'ID' simply so that the following description can easily refer to individual rows:

ID  col1 col2 col2 (RH)
R1  1    2    3    x'23'
R2  2    3    4    x'23'
R3  3    5    7    x'34'

As I've shown above, even with a UPI it is possible (and it happens with large tables) that more than one row will have the same row hash (RH) value.

Assume that you want to insert a new row and it so happens that the row hash of this new row is also x'23'.

For the insert processing, the dbms will only check the rows R1 and R2 because they are the only rows with the same row hash. It will not check ow R3.

 

Does that help?

 

Cheers,

Dave

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