Analyze Maps | Teradata Vantage - Analyzing Maps - Advanced SQL Engine - Teradata Database

Consider analyzing table groups by either database, application, or query band.

To do this, call CreateMapListSP to create the empty list. For example:

CALL tdmaps.CreateMapListSP('MyMapList', NULL, :ListId);

To do this, call CreateExpansionMaps to insert the three planned maps into TDMaps.Maps. For example, if you are expanding the system from two to four nodes, and the number of AMPs on the expanded system is 40:

CALL TDMaps.CreateExpansionMaps(4,40,:ContiguousMapString,:OneAmpSparseMapString,:TotalNodesSparseMapString);

This creates the following placeholder names for the three planned maps, which must be renamed after the system expansion (see Renaming Maps after a System Expansion:

• PREEXPANSIONMAP_CONTIGUOUSMAP_4NODES
• PREEXPANSIONMAP_1AMPSPARSEMAP_4NODES

• PREEXPANSIONMAP_4AMPSSPARSEMAP_4NODES

To do this, call AddMapListEntrySP. For example:

CALL tdmaps.AddMapListEntrySP('MyMapList', 'MySparseMap');

To do this, first call CreateObjectListSP to create the empty list. For example:

CALL tdmaps.CreateObjectListSP('MyObjectList', NULL, :ObjectListId);

Next, call AddObjectListEntrySP to add tables to the list. For example:

CALL tdmaps.AddObjectListEntrySP('BillsList','Bill%',NULL,:NumObjectsAdded);

This is useful when you want to consider an entire database for map reassignment but exclude a few tables in that database. First, call CreateExclusionListSP to create an empty list of tables to exclude. Then call AddExclusionListEntrySP to add selected tables to the exclusion list. For example:

CALL tdmaps.CreateExclusionListSP('BillsList', NULL, :ListId);

To do this, call AnalyzeSP to put recommended actions in TDMaps.ActionsTbl. For example:

-- Analyze tables in Personnel object list for queries logged over the
-- last 7 days and recommend actions for moving them into MyNewMap
--
CALL TDMaps.AnalyzeSP(
   'MyNewMapList', 'Personnel',NULL,
	CAST(CURRENT_TIMESTAMP – INTERVAL '7' day AS TIMESTAMP),
   CURRENT_TIMESTAMP,
	'DBC', 'MyNewMapActions', :NumAlters, :NumExcludes, :NumLogEntries);

Analysis is typically very fast if you did not enable DBQL step logging. If the analysis is considering DBQL input, the speed of the analysis depends on how many rows are in the table.