ALTER TABLE Examples (Map and Colocation Form) | Teradata Vantage - Examples - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Alter a Table to a Sparse Map

In this example, a table is populated with only 5 rows. A sparse map named OneAMPMap has been created. Following is the table definition:
CREATE SET TABLE MyDatabase.Tab1, FALLBACK,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT,
   DEFAULTMERGEBLOCKRATIO,
   MAP = TD_Map1
( A1 INTEGER,
  B1 INTEGER,
  C1 INTEGER
) PRIMARY INDEX(A1);
This statement moves the table to the sparse map and the colocation name defaults to MyDatabase_Tab1:
ALTER TABLE Tab1, MAP=OneAMPMap;

Alter Two Tables to a Sparse Map

In this example, two tables are populated with only 5 rows each and often joined on A1=A2. A sparse map named OneAMPMap has been defined. This example uses the following table definitions:
CREATE SET TABLE MyDatabase.Tab1, FALLBACK,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT,
   DEFAULTMERGEBLOCKRATIO,
   MAP = TD_Map1 
( A1 INTEGER,
  B1 INTEGER,
  C1 INTEGER
) PRIMARY INDEX(A1);
CREATE SET TABLE MyDatabase.Tab2, FALLBACK,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT,
   DEFAULTMERGEBLOCKRATIO,
   MAP = TD_Map1 
( A2 INTEGER,
  B2 INTEGER,
  C2 INTEGER
) PRIMARY INDEX(A2);
Because the tables are joined on the primary index, you want to colocate the tables. For table Tab1, the colocation name defaults to MyDatabase_Tab1. For table Tab2, you set the colocation name to MyDatabase_Tab1. The following statements colocate the tables:
ALTER TABLE Tab1, MAP=OneAMPMap;
ALTER TABLE Tab2, MAP=OneAMPMap COLOCATE USING MyDatabase_Tab1;