select_clause - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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.

The SELECT clause projects the columns from one or more base tables that are to be included in the join index definition.

You cannot specify the following options in this SELECT clause:
  • TOP n, FULL OUTER JOIN, CROSS JOIN, HAVING, QUALIFY, and SAMPLE
  • Any function except RANDOM
  • Statistical functions
  • Aggregate functions other than SUM and COUNT
  • Set operators UNION, EXCEPT/MINUS, INTERSECT, and their derivatives
  • Subqueries

You cannot specify an EXPAND ON clause in the definition of a join index.

You cannot invoke an SQL UDF anywhere in the definition of a join index.

Certain optional clauses do not become part of the join index definition, for example, output FORMAT and TITLE phrases. You can determine if an option you specify does not become part of the stored join index definition by performing a SHOW JOIN INDEX request to view the DDL for the CREATE JOIN INDEX statement.

For a complete description of the SELECT statement, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

COLUMN
Column format for storing a column partition.
As a general rule, Vantage 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, Vantage 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.