Cold Tables and MVC probability

Database
Teradata Employee

Cold Tables and MVC probability

Hi,

i've written a view that reads from the heatmap view (and function) and gets you the list of the cold tables (Teradata 14.10) so you can find a list of possible candidates to compress!

replace view mng.coldtables as (
select distinct databasename ,tablename, (sum(currentperm)/1024/1024/1024)PermSpace from dbc.tablesizev a
where tablename in (select tablename from mng.heatmap where temperature < 266)
and databasename not in (select databasename from dbc.dbase where rowtype = 'U')
group by databasename, tablename);

remember to tune the value 266 to the best temperature that suits your cold tables on your system

the heatmap view is the well-know (and shared in these forum by dnoeth i think?)

replace view mng.heatmap as
select date as thedate
, time as thetime
, t1.ampnumber
, dbase.databasenamei DatabaseName
, tvm.tvmnamei TableName
, t1.starttableid
, t1.starttableiduniq
, t1.starttableidtypeandindex
, t1.startpartition
, t1.startrowid
, t1.endtableid
, t1.endtableiduniq
, t1.endtableidtypeandindex
, t1.endpartition
, t1.endrowhash
, to_byte( t1.cylinderidmsw)||to_byte(t1.cylinderidlsw) CylinderId
, t1.cylinderidmsw
, t1.cylinderidlsw
, t1.temperature
, t1.normalizedtempinfo
, t1.requestedtempinfo
, t1.veryhotcandidate
, t1.veryhotcache
, t1.tempwarmceiling
, t1.tempwarmfloor
, t1.tempveryhotfloor
, t1.percentfull
from table(syslib.td1410_heatmap(hashamp(30)))
as t1(
AmpNumber,
StartTableId,
StartTableIdUniq,
StartTableIdTypeAndIndex,
StartPartition,
StartRowId,
EndTableId,
EndTableIdUniq,
EndTableIdTypeAndIndex,
EndPartition,
EndRowHash,
CylinderIdMsw,
CylinderIdLsw,
Temperature,
NormalizedTempInfo,
RequestedTempInfo,
VeryHotCandidate,
VeryHotCache,
TempWarmCeiling,
TempWarmFloor,
TempVeryHotFloor,
PercentFull),
dbc.tvm tvm,
dbc.dbase dbase
where t1.starttableiduniq = tvm.tvmid
and tvm.databaseid = dbase.databaseid;

 that's using the function td1410_heatmap (in downloads.)

to use the mng.COLDTABLES

select * from mng.coldtables where permspace > 100 order by permspace desc

(100 is in GB)