table_option (CREATE JOIN INDEX) - Teradata Vantage

Table options provide basic information about the join index being created: the containing database, the name of the join index, whether fallback-protection is enabled, whether the data blocks are compressed, and whether disk I/O integrity checking is enabled.

MAP

You can specify an existing contiguous or sparse map for the join index.

Optionally, you can specify a collocation name. A join index can have different map or colocation name than the base tables.

map_name

Name of an existing contiguous or sparse map.

You cannot specify TD_DataDictonaryMap or TD_GlobalMap.

COLOCATE USING colocation_name

Name for colocating the join index on the same AMPs with other tables or join indexes.

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 join index. If database exceeds 63 characters, database is truncated to 63 characters. If index exceeds 64 characters, index is truncated to 64 characters.

FALLBACK

The join index uses fallback protection. 
FALLBACK is the default. 
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. Active Fallback may be able to repair the damage to the primary data dynamically. If 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.

PROTECTION

An optional keyword.

NO

If a join index is not fallback protected, an AMP failure prevents the following events from occurring:

• Processing queries that use the join index.
• Updating the base table on which the join index is defined.

You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.

CHECKSUM

A table-specific disk I/O integrity checksum level. The checksum setting applies to primary data rows, fallback data rows, and all secondary index rows for the join index.

ON

Calculate checksums using the entire disk block. Sample 100% of the disk blocks to generate a checksum.

OFF

Disables checksum disk I/O integrity checks.

DEFAULT

This is set by the system.

BLOCKCOMPRESSION

Block-compress the data in the join index, including compression that occurs based on the temperature of the cylinders on which the data is stored. The definitions of the thresholds are determined by the system.

AUTOTEMP

The file system determines the block-level compression setting for the join index.

DEFAULT

The join index uses the compression option set by the system. The value of DefaultTableMode is not saved in the join index definition as part of a CREATE JOIN INDEX request, so a join index set to DEFAULT is affected by any future change to the DefaultTableMode parameter.

MANUAL

Block level compression is applied based on the default for the join index at the time the join index is created. Data inserted into the existing join index inherits the current compression status of the join index at the time the data is inserted.

NEVER

The join index is not compressed.