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.
Highlighted
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);

9 REPLIES
Senior 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
Senior 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?

 

Senior 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

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

My question is I have a set table with a PI, not a UPI.  So when loading the table does it dow a row level check for only the rows with the match PI, or does a full table check regardless of the PI?.  Would seem logical it would only check for duplicate rows based on the PI, much smaller subset than a full table row check everytime. 

Senior Apprentice

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

Hi,

 

As per the previous posts, 'duplicate row checking' is not a full table scan. The dbms only checks those rows which have the same rowhash as the one being inserted/updated.

 

HTH

Dave

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

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

Dave,

 

let me go one step further.  If I have a NUPI on the table and a PPI will the duplicate row check first go to the block of partition of the PPI and then do a hash map row check based on the NUPI?

 

v/r

jack

Teradata Employee

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

Correct. Only rows with the same RowHash are checked to see if they are full row duplicates. (Generally, that's rows with the same PI values; rarely you may have a "hash collision" where a different PI generates the same RowHash.)

Senior Apprentice

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

Hi Jack,

Yes, the check will only be within a single partition, and then only for those row(s) with the same rowhash.

Cheers,

Dave

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