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.
- Keyword that precedes the definition of base table column or an expression to be included in the join index.
- Definition of base table column or an expression to be included in the join index.
- 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.
- 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.
- 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 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 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 autocompression value specified after COLUMN keyword that begins the column partitioning specification. If you do not specify an autocompression value there, the system default is AUTO COMPRESS.
- For more information about autocompression, see Teradata Vantage™ - Database Design, B035-1094.