ON Clause - Teradata Database - Teradata Vantage NewSQL Engine

You can have up to 16 ON clauses. You can use multiple ON clauses with all supported languages for protected and non-protected mode table operators.

If you use multiple ON clauses, the order of the ON clauses must match the order of the stream numbers in the table operator that you use.

Cogroups are used for table operators with multiple ON clauses. For information about cogroups, see Teradata Vantage™ SQL Operators and User-Defined Functions, B035-1210.

You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.

See Function Mapping and the ON Clause.

table_name

view_name

Table or view expression that is input to the table operator.

(query_expression)

Query expression that is input to the table operator.

AS correlation_name

Alias for the table.

HASH_BY column

column_position

column_expression

Optional set of column names on which to hash order globally the columns input to a table function or table operator.

You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.

When you use a multiple input table operator that has multiple hash by clauses, the following restrictions apply:

• All of them must have the same number of partitioning attributes.
• The corresponding attributes must be the same type or matched using an implicit cast.

If you specify a LOCAL ORDER BY clause along with a HASH BY clause, the following restrictions apply:

• All the clauses must have the same number of LOCAL ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

PARTITION BY column_name

column_position

column_expression

Partition for a table specified as input to a table operator.

You can specify a column by name or position, or use an expression that resolves to a column.

You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.

You cannot specify a row-level security constraint column as a partitioning column.

If you have multiple PARTITION BY clauses, the following restrictions apply:

• All of them must have the same number of partitioning attributes.
• The corresponding attributes must be the same type or matched using an implicit cast.

When you specify an ORDER BY clause with a PARTITION BY clause, the following restrictions apply:

• All the clauses must have the same number of ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

Partition keys are important to performance. If the partition keys hash to the same AMP, then some AMPs may be overloaded compared with others. Performance may also be affected if the partition key is a VARCHAR, because VARCHARs may take longer to compare than some other data types.

PARTITION BY ANY

Specifies a table with no partitioning or order by attributes.

PARTITION BY ANY preserves the existing distribution of the rows on the AMPs. A PARTITION BY ANY clause followed by ORDER BY clause means that all the rows are ordered by the ORDER BY clause on that partition, and it is functionally equivalent to using LOCAL ORDER BY without using a HASH BY clause.

The following restrictions apply:

• You cannot specify a PARTITION BY ANY clause and a LOCAL ORDER BY clause in the same ON clause.
• If you specify multiple ON clauses with a table operator, you can only specify one PARTITION BY ANY clause. All other clauses must be DIMENSION.

DIMENSION

Specifies that a duplicate copy of the dimension table is created for every partition on which the function operates.

You can specify zero or more DIMENSION clauses for each ON clause.

DIMENSION input is useful when the input table is a small look up table for facts or is a trained model, such as that used for machine learning. For a look up table using DIMENSION, the rows are duplicated to all the AMPs. Each AMP holds one instance of the look up table in memory and uses it to process each row of another input.

For machine learning, you can store a model in the database that predicts the outcome of a particular data set and then use the stored model as dimensional input to a function.

The following restrictions apply:

• You cannot use a LOCAL ORDER BY clause and a DIMENSION clause in the same ON clause.
• If you have only one ON clause as input to a table operator, you cannot use DIMENSION in it. You must have at least one PARTITION BY or HASH BY clause in a second ON clause to use DIMENSION.
• If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, then all other ON clauses can only specify DIMENSION.
• If you use the SCRIPT table operator, you cannot use a DIMENSION in the ON clause.

  For information about the SCRIPT table operator, see Teradata Vantage™ SQL Operators and User-Defined Functions, B035-1210.

LOCAL ORDER BY column_name

column_position

column_expression

Qualified rows are ordered on each AMP in preparation to be input to a table function.

You cannot specify a PARTITION BY ANY clause with a LOCAL ORDER BY clause in the same ON clause.

You cannot specify a DIMENSION clause with a LOCAL ORDER BY clause in the same ON clause.

If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, all other ON clauses can only specify the DIMENSION option.

If you use multiple ON clauses, you cannot use a LOCAL ORDER BY clause in addition to a DIMENSION clause in the same ON clause.

If you specify multiple HASH BY clauses with LOCAL ORDER BY clauses, the following restrictions apply:

• All the clauses must have the same number of LOCAL ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

ORDER BY

Defines how result sets are sorted. If you do not use this clause, result rows are returned unsorted.

You cannot specify ORDER BY as the only option in an ON clause. You must combine it with a PARTITION BY, PARTITION BY ANY, HASH BY, or DIMENSION clause.

If you specify an ORDER BY clause along with a PARTITION BY clause, the following restrictions apply:

• All the clauses must have the same number of ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

When NORMALIZE and ORDER BY are specified in a query, the rows are normalized first and the result is ordered on the normalized result.