ALTER TABLE Examples (Map and Colocation Form) | Teradata Vantage - Examples - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
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;