16.20 - 例: 特定のテーブルを持つAMPの識別 - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL演算子およびユーザー定義関数

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
管理
featnum
B035-1210-162K-JPN

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