Use Consistent Predicate Domains - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Use Consistent Predicate Domains

You should always be consistent with the predicate domains you specify in a WHERE clause search condition. Specifying appropriate predicate domains enables Teradata Database to avoid having to perform an implicit conversion on a value before comparing it.

For example, if emp_no is defined as CHARACTER(5), then you should compare emp_no values using character values in the WHERE clause search condition rather than numeric values, as indicated in this predicate, where the value 12345 is correctly specified as a character string (‘12345’) rather than as a numeric value (12345).

     …
     WHERE emp_no = '12345'
     …

The efficiency of a join operation depends on whether the WHERE condition uses values for columns on which primary, secondary, or multitable join indexes are defined.

If indexes are defined on the dept_no columns in both the employee and department tables, specifying an equality condition between the values in each indexed column, as in the preceding example, allows the rows in the two tables to be matched using the values in both indexed columns.

Efficiency is increased if a primary index is defined on one or both of these columns. For example, define dept_no as the unique primary index for the department table. This is not possible if one or both of the tables being joined is an ordinary NoPI table or a NoPI column‑partitioned table. See “NoPI Tables, Column‑Partitioned Tables, and WHERE Clause Search Conditions” on page 106 for suggestions about how to work around this potential problem.

For all-AMP tactical queries against row-partitioned tables, you should specify a constraint on the partitioning column set in the WHERE clause.

If a query joins row-partitioned tables that are partitioned identically, using their common partitioning column set as a constraint enhances join performance still more if you also include an equality constraint between the partitioning columns of the joined tables.

NoPI and NoPI column‑partitioned tables require a full‑table scan to retrieve rows. Column partition elimination or row partition elimination may occur that make the scans less than full-table scans.

Because NoPI and NoPI column‑partitioned tables support USIs and NUSIs, consider specifying USIs for conditions designed to retrieve single rows and NUSIs for conditions designed to retrieve row sets. For details, see “NoPI Tables and SELECT Statements” on page 16.

Although you cannot specify join indexes in WHERE clause conditions, the Optimizer uses them if they exist and can be used to avoid a full‑table scan. For information about how the Optimizer uses join indexes, see SQL Request and Transaction Processing.

Defining join indexes on a NoPI or NoPI column‑partitioned table can slow down the loading of rows into the table.

An unconstrained join is one for which a WHERE clause is not specified for the tables that are joined. See “Cross Join” on page 233.

The result of an unconstrained join is a Cartesian product, which is rarely the desired result. A Cartesian product is the product of the number of rows in each table that is joined. An unconstrained join can produce a great many rows, returning a result that not only is not desired, but one that places a large performance burden on the system.

If a SELECT statement specifies correlation and real names (for example, correlation names in a WHERE clause and real names in the select list), the Optimizer may specify an unconstrained join, depending on what the conditions of the query are and how they are specified.