Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
English (United States)
Last Update
Product Category
Teradata Vantage™

Altering the Table to a Sparse Map Without Specifying the Colocation Option

If you alter the table to use a sparse map without specifying the COLOCATE USING clause and the table previously used a sparse map, the colocation name does not change. Otherwise, the colocation name defaults to database_table, where database is the name of the database or user followed by an underscore (_) and table is the name of the table. If database exceeds 63 characters, database is truncated to 63 characters. If table exceeds 64 characters, table is truncated to 64 characters.

Locks During the Alter Table Map Operation

A read lock is placed on the table during the copy to the new map. The lock is upgraded to an exclusive lock while DBC.TVM is updated and the transaction is committed.

Indexes and the Alter Table Map Operation

Secondary indexes for the table are moved or rebuilt using the new map.

Join indexes or hash indexes on the base table are not moved when the table is moved to a new map. You must use ALTER JOIN INDEX or ALTER HASH INDEX, as appropriate.

System-defined join indexes for the table are moved or rebuilt using the new map.

The table can have a primary index, primary AMP index (PA), or no primary index (NoPI). The table can have column partitioning, row partitioning, or both. NoPI and PA table rows are redistributed into the new map.

Join indexes and hash indexes that have the ROWID field from a PA, NoPI table, or from a column-partitioned PI table that is altered to a new map are invalidated. You must drop and recreate the join index or hash index.

Secure Zones and Sparse Maps

For a sparse map, you must be in the same secure zone as the sparse map.

Permanent Journaling and Altering the Table Map

If permanent journaling is enabled for a table, the map for the table must be a contiguous map and the map must be the same as the map of the journal table associated with the table. A journal table must have a contiguous map.

To alter the map of a table that has journaling enabled, you must alter the table to have NO JOURNAL and alter the table to another contiguous map. Then, you can enable journaling again. If you alter a table to a sparse map, you cannot enable journaling.