16.10 - Analyzing Maps - Teradata Database

Teradata Database Administration

Product

Teradata Database

Release Number

16.10

Published

April 2018

Language

English (United States)

Last Update

2018-04-26

dita:mapPath

qjg1509413559832.ditamap

dita:ditavalPath

changebar_rev_16_10_exclude_audience_ie.ditaval

dita:id

ujp1472240543947

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 map list. To do this, call CreateMapListSP to create the empty list. For example:
```
CALL tdmaps.CreateMapListSP('MyMapList', NULL, ListId);
```
Add an entry to the map list. To do this, call AddMapListEntrySP. For example:
```
CALL tdmaps.AddMapListEntrySP('MySparseMapList', 'MySparseMap');
```
Build a list of objects to analyze. To do this, first call CreateObjectListSP to create the empty list. For example:
```
CALL tdmaps.CreateObjectListSP('BillsList', NULL, ListId);
```
Next, call AddObjectListEntrySP to add tables to the list. For example:
```
CALL tdmaps.AddObjectListEntrySP('BillsList','Bill%',NULL,NumObjectsAdded);
```
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);
CALL tdmaps.AddExclusionListEntrySP('BillsList','Bill%',null,NumObjectsAdded);
```
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 SQL Functions, Operators, Expressions, and Predicates.