17.10 - selection - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)
database_name.table_name
user_name.table_name
The fully qualified path to column_name, if required to uniquely identify the column_name or ROWID.
A join index can reference a maximum of 1 row level security table. If you create a join index on a row -level security table, you must include all security constraint columns for the table in the index definition.
column_name
The name of a base table column or an expression to be included in the join index.
You can include columns in the join index with a data type of non-LOB XML, non-LOB ST_GEOMETRY, non-LOB JSON, and non-LOB DATASET. However, you cannot include these data types in the primary index of a join index.
A column in a join index cannot have a data type of BLOB, CLOB, BLOB-based UDT, CLOB-based UDT, VARIANT_TYPE, ARRAY, VARRAY, LOB XML, LOB ST_GEOMETRY, LOB JSON, or LOB DATASET.
You can specify a user-defined column named partition or partition#L-n, where n ranges from 1 through 62.
However, you cannot include the system-derived columns PARTITION or PARTITION#L n in the column_name list.
A join index defined with an expression in its select list has more restricted coverage than a join index defined using a base table column.
However, if you define a complex expression in the definition of a single-table join index, you should always collect statistics on it because the Optimizer can use those statistics directly to estimate the selectivity of those expressions for base table expressions specified in a query predicate. For more information, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
You can base an expression on a UDT column or an expression that references at least one column with the following exceptions:
  • Expressions that involve aggregate or OLAP functions
  • UDF expressions
  • Built-in functions or keywords that are explicitly prohibited, such as DEFAULT or PARTITION

    For information about the DEFAULT built-in function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. For information about the PARTITION keyword, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. These expressions must be aliased. Otherwise, the system returns an error to the requestor.

    For two columns with the same name, you must alias both names using a column name alias. See Teradata Vantage™ - SQL Fundamentals, B035-1141.

    If you also define a repeating group column_name list, the system stores each distinct value of the column_name list only once.

  • If you define a repeating group column_name list, the limit on the total number of columns you can specify for the fixed group column_name list is 64.
  • If you do not define a repeating group column_name list, there is no defined limit on the total number of columns you can specify for the fixed group column_name list.

    The maximum is restricted only by the number of columns that can be defined within the row size limit.

    This limit is not related to the restriction of 64 columns that can be defined per referenced base table per join index.

You can specify a maximum of 64 columns per referenced base table per join index.
ROWID
Provides the internal row identifier associated with a row of a base table. The data type of the ROWID column is BYTE(10) except for 8-byte partitioning, where it is BYTE(16). Specify the keyword ROWID as one of the values for either the fixed column_name list or the repeating column_name list to enable the Optimizer to join a partial covering index to its base table to access any non-covered columns.
If the join index has a primary index, whether partitioned or nonpartitioned, you can optionally specify an alias for the column_name and the system-derived ROWID column.
If the join index is a column-partitioned join index, the following rules apply.
  • You must specify the system-derived ROWID column and you must also specify an alias for it.
  • You can group columns together in the select list of the definition by delimiting them with parentheses. Vantage stores the specified columns together in the same column partition.

    You can also group columns in a COLUMN specification of a PARTITION BY clause for the join index, but you cannot group columns in the select list and the COLUMN clause for a column-partitioned join index.

If you reference multiple tables in the join index definition, you must fully qualify each ROWID specification.
A GROUP BY clause that references a ROWID must have a column name alias. The literal ROWID is not valid in the column list argument of a GROUP BY specification.
If you reference a ROWID column name alias in the select list of a join index definition, you can also reference that column name alias in a CREATE INDEX request that creates a secondary index on the join index. However, you cannot directly reference the ROWID keyword in a CREATE INDEX request.
You can only specify ROWID in the outermost select list of a CREATE JOIN INDEX statement.