17.10 - index - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)
You can specify an existing contiguous or sparse map for the hash index.
For a sparse map, you can specify a colocation name. A hash index can have different map or colocation name than the base tables.
Name of an existing contiguous or sparse map.
You cannot specify TD_DataDictonaryMap or TD_GlobalMap.
Name for colocating the hash index on the same AMPs with other tables, join indexes, or hash indexes. For example, you can colocate two tables, then join the tables on the primary index or primary AMP index columns.
You can only specify this option for a sparse map. For a contiguous map, the colocation_name is not needed for colocation and is set to NULL.
If you do not specify a colocation name, the name defaults to database_index, where database is the name of the database or user followed by an underscore (_) and index is the name of the hash index. If database exceeds 63 characters, database is truncated to 63 characters. If index exceeds 64 characters, index is truncated to 64 characters.
The hash index does not use fallback protection.
You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
If a hash index is not fallback protected, an AMP failure prevents the following events from occurring:
  • Processing queries on the hash index
  • Updating the table on which the hash index is defined
The hash index uses fallback protection.
The default is to use the fallback specification for the database in which the hash index is defined.
When a hardware read error occurs, the file system reads the fallback copy of the data and reconstructs the rows in memory on their home AMP. Support for Read From Fallback is limited to the following cases:
  • Requests that do not attempt to modify data in the bad data block
  • Primary subtable data blocks
  • Reading the fallback data in place of the primary data. In some cases, Active Fallback can repair the damage to the primary data dynamically. In situations where the bad data block cannot be repaired, Read From Fallback substitutes an error-free fallback copy of the corrupt rows each time the read error occurs.

Optional keyword.

A table-specific disk I/O integrity checksum level. The checksum setting applies to primary and fallback data rows for the hash index.
If you do not specify a value, the system assumes the system-wide default value for this table type. The result is identical to specifying DEFAULT. If you are changing the checksum for this table to the system-wide default value, then specify DEFAULT.
Calculate checksums using the entire disk block. Sample 100% of the disk blocks to generate a checksum.
The default setting is the current DBS Control checksum setting specified for this table type.
Disables checksum disk I/O integrity checks.
Specifies whether the data in the hash index should be block-compressed based on the temperature of the cylinders on which it is stored.
You can specify the following settings for block_compression_option.
The compressed state of the data in the hash index can be changed by Vantage at any time based on its temperature.
You can still issue query band options or Ferret commands, but if the compressed state of the data does not match its temperature, such changes might be undone by the system over time.
The hash index uses the compression option (MANUAL, AUTOTEMP or NEVER) set in the DBS Control parameter DefaultTableMode. For details, see Teradata Vantage™ - Database Utilities, B035-1102. Note that value of DefaultTableMode is not saved in the hash index definition as part of a CREATE HASH INDEX request, so a hash index set to DEFAULT is affected by any future change to the DefaultTableMode parameter.
That block level compression is applied based on the default for the hash index at the time the hash index is created. Hash indexes can be compressed or uncompressed at any time after loading by using the Ferret COMPRESS and UNCOMPRESS commands. Data inserted into the existing hash index inherits the current compression status of the hash index at the time the data is inserted.
The hash index is not compressed even if the DBS Control block compression settings indicate otherwise. Vantage rejects Ferret commands to manually compress the hash index, but Ferret commands to decompress the index are valid.