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

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
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.