16.20 - CREATE MAP - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

Purpose

Creates a sparse map.

Sparse maps are intended for small tables, join indexes, and hash indexes. You can colocate tables, join indexes, or hash indexes on the same AMPs. Tables that have the same sparse map and colocation name can be joined using the same primary index or primary AMP index columns without redistributing or duplicating rows. For example, you can colocate two tables, then join the tables on the primary index or primary AMP index columns. See the CREATE TABLE MAP option, ALTER TABLE Map and Colocation, the CREATE JOIN INDEX MAP option, and the CREATE HASH INDEX MAP option.

You can also specify a sparse map for a table operator when a table operator is included in the FROM clause of a SELECT statement. See "Table Operator" in Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .

Required Privileges

You must have the CREATE MAP privilege. You must have been granted the contiguous map that you specify in this statement. See "GRANT MAP" in Teradata Vantage™ SQL Data Control Language, B035-1149.

Privileges Automatically Granted

The map creator is granted the map WITH GRANT OPTION that allows the creator to grant or revoke the created sparse map to users, PUBLIC, and roles.

Syntax



Syntax Elements

sparse_map_name
Name of the sparse map.
You cannot specify a name of an existing map or a name that begins with TD_.
For best practice, include "map" in the name, for example OneAMPMap or SmallTable_Map.
FROM contiguous_map_name
Name of the parent contiguous map for the sparse map. You must specify the name of an existing contiguous map.
You cannot specify TD_DataDictonaryMap or TD_GlobalMap.
SPARSE
Required keyword to indicate that the map is a sparse map.
AMPCOUNT = n
Specifies the number of AMPs for the map, where n is a value from 1 to the number of AMPs in the parent contiguous map, up to 1024.

Example

This statement creates a 4-AMP sparse map for small tables.

CREATE MAP Small_Table_Map FROM TD_Map1 SPARSE AMPCOUNT=4;

Usage Notes

A sparse map is specific to the secure zone of the creator.

You cannot use the CREATE MAP statement to create a contiguous map.