CREATE JOIN INDEX Syntax | Teradata Vantage - CREATE JOIN INDEX Syntax - Teradata Vantage - Analytics Database

CREATE JOIN INDEX [ database_name. | user_name. ] join_index_name
  [ table_option [,...] ]
  select_clause  
  FROM source [,...]
  [ WHERE search_condition ]
  GROUP BY grouping_or_ordering_specification [,...]
  ORDER BY grouping_or_ordering_specification [,...]
  [ index [[,]...] ] [;]

table_option

{ MAP = map_name [ COLOCATE USING colocation_name ] |
  [NO] FALLBACK [ PROTECTION ] |
  CHECKSUM = integrity_checking_level |
  BLOCKCOMPRESSION = block_compression_option
}

select_clause

AS SELECT
  { selection [,...] |
    ( selection [,...] ) , ( selection [,...] ) |
    [ COLUMN | ROW ] ( selection [,...] ) [ [NO] AUTO COMPRESS ]
  }

source

{ [ database_name. | user_name. ] table_name [ [AS] corrolation_name ] |
  joined_table
}

grouping_or_ordering_specification

{ column_name | column_position | column_alias | expression_alias }

index

{ [ UNIQUE ] PRIMARY INDEX [ index_name ] ( primary_index_column [,...] ) |
  NO PRIMARY INDEX |
  PRIMARY AMP [ INDEX ] [ index_name ] ( index_column_name [,...] ) |
  PARTITION BY { partitioning_level | ( partitioning_level [,...] ) } |
  INDEX [ index_name ] [ ALL ] ( index_column_name [,...] )
    ORDER BY [ VALUES | HASH ] [ ( order_column_name ) ]
}

selection

[ [ database_name. | user_name. ] table_name ] { column_name | ROWID } | aggregation_clause }

joined_table

{ ( joined_table ) |

  joined_table [ INNER | { LEFT | RIGHT } [ OUTER ] ]
    JOIN joined_table ON search_condition |

  table_name [ [AS] correlation_name ]
}

partitioning_level

{ partitioning_expression |
  COLUMN [ [NO] AUTO COMPRESS ] [ [ ALL BUT ] column_partition ]
} [ ADD constant ]

aggregation_clause

{ expression |
  SUM ( numeric_expression ) |
  { COUNT | MIN | MAX } ( value_expression ) |
  EXTRACT ( { YEAR | MONTH } FROM date_expression )
} [ [AS] expression_alias ]