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

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

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
2019年3月
Language
日本語
Last Update
2019-10-30
dita:mapPath
ja-JP/drp1544241916620.ditamap
dita:ditavalPath
ja-JP/drp1544241916620.ditaval
dita:id
B035-1210
Product Category
Software
Teradata Vantage

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