15.10 - Join Index Select List - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The select list of a join index specification defines the list of columns and expressions that make up the definition for the index.

You can also use the select list of your join index definition to create an aggregate join index by using the SUM, MIN, MAX, and COUNT aggregate functions.

AS
Keyword that precedes the definition of base table column or an expression to be included in the join index.
SELECT
Definition of base table column or an expression to be included in the join index.
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.
A column in a join index cannot have a BLOB, CLOB, JSON, XML, BLOB-based UDT, CLOB-based UDT, XML-based UDT, VARIANT_TYPE, ARRAY, VARRAY, or Geospatial data type.
Although you cannot include entire columns with the JSON data type in a join index, you can include extracted portions of the JSON document in the join index. See Teradata JSON.
You cannot include the system-derived columns PARTITION or PARTITION#Ln in the column_name list.
However, you can specify a user-defined column named partition or partition#L-n, where n ranges from 1 through 62.
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 details, see 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 that are explicitly prohibited, such as DEFAULT and PARTITION

    For details about these functions, see SQL Functions, Operators, Expressions, and Predicates, B035-1145SQL Functions, Operators, Expressions, and Predicates. 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 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. Teradata Database 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.
COLUMN
Column format for storing a column partition.
As a general rule, Teradata Database assigns COLUMN format to narrow column partitions and ROW format to wide column partitions.
COLUMN preceding a column grouping in the select list of the join index definition stores the column or column group in containers using COLUMN format.
If you do not specify COLUMN or ROW, the system determines whether to use COLUMN or ROW format based on the width of the column value and other factors.
ROW
Row format for storing a column partition.
ROW preceding a column in the select list of the join index definition stores the column in subrows using ROW format.
AUTO COMPRESS
NO AUTO COMPRESS
If you do not specify AUTO COMPRESS or NO AUTO COMPRESS, Teradata Database uses the AUTO COMPRESS or NO AUTO COMPRESS specified after COLUMN keyword that begins the column partitioning specification. If you do not specify autocompression, the system uses the autocompression default defined by the cost profile constant AutoCompressDefault.
For more information about autocompression, see Database Design, B035-1094.
For information about AutoCompressDefault, see SQL Request and Transaction Processing, B035-1142.