Skip to main content

Datometry Documentation

Override Teradata SET and UPI Table Emulation

You can override Teradata SET and UPI table emulation using the Hyper-Q hint override_table_uniqueness. Using override_table_uniqueness, you can:

  • Override both SET and Unique Primary Index (UPI) tables.

  • Override only SET tables.

  • Override only UPI tables.

  • Use both SET and UPI table definitions, which is the default Hyper-Q behavior.

You can override SET and UPI table emulation for all Teradata table types, such as regular, volatile, and global temporary tables. Additionally, the override_table_uniqueness hint supports all DML query types, such as INSERT, UPDATE, UPSERT, and bulk loading.

The default table creation for Teradata in certain circumstances is to create SET tables. SET tables obey set theory semantics and the relational data model by not permitting duplicate rows in a table. For this reason, when moving to a modern cloud database you may have too many SET tables on the target data warehouse. Hyper-Q emulates SET table behavior when performing DML queries, however, this may result in performance degradation. For this reason, Datometry recommends that you override (or ignore) SET tables. You can apply the SET table override at the statement, request, or session level, or you can apply it globally so all SET table emulation is ignored.

Important

If a table has intentionally been created as a SET table to support business logic in your applications, do not override SET table emulation for those tables as it may adversely affect query results.

Teradata Unique Primary Index (UPI) tables guarantee uniform distribution of table rows. If you create a table with primary index (PI) column, the PI is a column, or columns, that have no duplicate values. As with SET tables, you may have too many UPI tables when moving to a modern cloud database, which may cause performance degradation. For this reason, Datometry recommends that you override (or ignore) UPI tables.

Important

If a table has intentionally been created as a UPI table to support business logic in your applications, do not override UPI table emulation for those tables as it may adversely affect query results.

The syntax for override_table_uniqueness is:

override_table_uniqueness = ignore_all | ignore_upi | ignore_set | none

where:

Parameter

Description

ignore_all

Specifies that Hyper-Q override both SET and UPI tables.

ignore_upi

Specifies that Hyper-Q override only UPI tables.

ignore_set

Specifies that Hyper-Q override only SET tables.

none

Specifies that Hyper-Q allow both SET and UPI table definitions, which is the default behavior.

Hint Scope

The override_table_uniqueness hint can be specified at the statement, request, session, and global level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Example 30. Specify Hyper-Q SET and UPI Table Behavior as a Global Setting

You can specify override_table_uniqueness as a global setting in the Hyper-Q configuration file (dtm.ini). This example specifies ignore_all which overrides both SET and UPI tables.

Caution

Before specifying that Hyper-Q ignore both SET and UPI tables at a global level, ensure that your database does not intentionally use either SET or UPI tables to support business logic in your applications. If that is the case, you must specify that Hyper-Q ignore such tables on a case by case basis using the override_table_uniqueness parameter as a query hint.

"gateway".override_table_uniqueness=ignore_all

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters.Gateway Configuration Parameters



Example 31. Specify that Hyper-Q Ignore SET Tables as a Query Hint

You can specify override_table_uniqueness=ignore_set as a query hint. This example specifies that the hint be used to to override SET tables. In the example shown, a second INSERT statement is able to successfully update the table because duplicate rows are not prohibited due to the SET table override. If the behavior of the SET table were being observed, the second ISERT statement would generate an error as duplicate rows would not be allowed.

CREATE SET TABLE table_name (
-- @dtm_override_table_uniqueness=ignore_set
      idnum integer,
      phone integer,
      name varchar(10));
INSERT INTO table_name (1,2,'hello');
INSERT INTO table_name (1,2,'hello');  

To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.



Example 32. Specify that Hyper-Q Ignore UPI Tables as a Query Hint

This example overrides UPI tables by specifying override_table_uniqueness=ignore_upi. The query produces the duplicate row error DTM9060: Invalid update statement: 'Duplicate row error' with the attempted update as it is a SET table.

CREATE SET TABLE table_name (
-- @dtm_override_table_uniqueness=ignore_upi
      idnum integer,
      phone integer,
      name varchar(10));
INSERT INTO table_name (1,2,'hello');
INSERT INTO table_name (1,3,'hello');   
UPDATE table_name SET phone=2 where idnum=1;
-- The UPDATE query will error with DTM9060: Invalid update statement: 'Duplicate row error'


Example 33. Specify that Hyper-Q Ignore SET and UPI Tables as a Query Hint

This example overrides both SET and UPI tables. As both SET and UPI constraints are ignored, the update to the table performs without error.

CREATE SET TABLE table_name (
-- @dtm_override_table_uniqueness=ignore_all
      idnum integer,
      phone integer,
      name varchar(10));
INSERT INTO table_name (1,2,'hello');
INSERT INTO table_name (1,3,'hello');   
UPDATE table_name SET phone=2 where idnum=1;