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:
- Decide which tables to analyze for map efficiency. Consider analyzing table groups by either database, application, or query band.
Create a list of maps.
To do this, call CreateMapListSP to create the empty list. For example:
CALL tdmaps.CreateMapListSP('MyMapList', NULL, :ListId);
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:
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');
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:
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);
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.