Analyze Maps | Teradata Vantage - Analyzing Maps - Analytics Database - Teradata Vantage

Database Administration

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
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™
For maximum processing efficiency, tables need to be on the AMPs best suited to their size and join functions. To determine if tables should be moved to other AMPs, follow these steps to analyze how effectively tables are assigned to maps. These maps determine table placement on AMPs:
  1. Decide which tables to analyze for map efficiency.
    Consider analyzing table groups by either database, application, or query band.
  2. Create a list of maps.
    To do this, call CreateMapListSP to create the empty list. For example:
    CALL tdmaps.CreateMapListSP('MyMapList', NULL, :ListId);
  3. If you are doing this analysis to help you plan a system expansion, include pre-expansion (planned) maps in the map list and define the number of nodes and AMPs in the planned expanded system.
    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

  4. Add an entry to the map list, which can be a list of maps or a map name.
    To do this, call AddMapListEntrySP. For example:
    CALL tdmaps.AddMapListEntrySP('MyMapList', 'MySparseMap');
  5. Build a list of objects to analyze.
    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);
  6. Optionally, exclude tables from the list.
    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);
  7. Create a list of recommendations for reassigning tables to different maps.
    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.
    For more information on these procedures, including the syntax, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.