例: 特定のテーブルを持つAMPの識別 - Advanced SQL Engine - Teradata Database

SYSLIB.SparseMapAMPs関数を使用して、スパース マップ内の特定のテーブルがどのAMPに含まれるかを検索します。

WITH dt1 (DatabaseName, ObjectName, ObjectKind, MapSlot, NumberOfPrimaryAMPs, ColocationName) AS
(SELECT DatabaseName, TableName, TableKind, MapSlot, NumberOfAMPs,
        ColocationName
 FROM DBC.TablesV t, DBC.Maps m
 WHERE ColocationName IS NOT NULL
   AND t.MapName = m.MapName)
SELECT AmpNo, PF, CAST(DbName AS CHAR(5)), CAST(ObjName AS CHAR(5))
FROM TABLE (SparseMapAMPs(dt1.DatabaseName, dt1.ObjectName, dt1.ObjectKind, dt1.MapSlot, dt1.NumberOfPrimaryAMPs, dt1.ColocationName)) dt
ORDER BY 1;

 AmpNo  PF  DbName  ObjName
------  --  ------  -------
     0  P   db1     tabs2
     7  P   db1     tabs1
    24  F   db1     tabs2
    31  F   db1     tabs1
    72  F   db1     tabs2
    96  P   db1     tabs2

WITH dt1 (DatabaseName, ObjectName, ObjectKind, MapSlot, NumberOfPrimaryAMPs, ColocationName) AS
(SELECT DatabaseName, TableName, TableKind, MapSlot, NumberOfAMPs,
        ColocationName
 FROM DBC.TablesV t, DBC.Maps m
 WHERE ColocationName IS NOT NULL
   AND t.MapName = m.MapName)
SELECT CAST(DbName AS CHAR(12)), CAST(ObjName AS CHAR(12)), AmpNo, PF
FROM TABLE (SparseMapAMPs(dt1.DatabaseName, dt1.ObjectName,      dt1.ObjectKind, dt1.MapSlot, dt1.NumberOfPrimaryAMPs,             dt1.ColocationName)) dt
ORDER BY 1,2;

DbName        ObjName        AmpNo  PF
------------  ------------  ------  --
db1           tabs1              7  P
db1           tabs1             31  F
db1           tabs2              0  P
db1           tabs2             24  F
db1           tabs2             72  F
db1           tabs2             96  P