Scewed tables

/*
Query #1 for getting skewed table in all databases, order by skew desc
Field SQL_FOR_ADDITIONAL_INFO can be used for getting object structure
*/
SELECT sod.objid,
sod.createdate,
sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.objtype,
soss.skew,
soss.used_bytes/1024/1024/1024 used_gbytes,
‘SELECT t.relname, a.attname column_name, FORMAT_TYPE(a.atttypid, a.atttypmod) type_name, dm.distseqno,’||CHR(13)||CHR(10)||
‘ CASE t.reldistmethod WHEN 6003 THEN ”CENTRAL_HOST” WHEN 6005 THEN ”RANDOM” WHEN 6004 THEN ”ATTRIBUTES” ‘||
‘ELSE ”UNKNOWN” END reldistmethodname ‘||CHR(13)||CHR(10)||’FROM ‘||sod.dbname||’.._t_class t’||CHR(13)||CHR(10)||’LEFT JOIN ‘||
sod.dbname||’.._t_attribute a ON t.oid = a.attrelid’||CHR(13)||CHR(10)||’LEFT JOIN ‘||sod.dbname||
‘.._t_dist_map dm ON dm.relid = a.attrelid AND dm.distattnum = a.attnum ‘||CHR(13)||CHR(10)||’WHERE t.oid = ‘||
CAST(sod.objid AS VARCHAR(38))||CHR(13)||CHR(10)||’ORDER BY a.attnum’ sql_for_additional_info
FROM _v_sys_object_data sod
INNER JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
INNER JOIN _v_sys_database d ON sod.dbname = d.objname
/*WHERE soss.skew > 50*/
ORDER BY soss.skew DESC
LIMIT 100;
/*
Query #2 for getting skewed database objects in current databases, order by skew desc
*/
SELECT sod.objid,
sod.createdate,
sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_gbytes,
soss.skew,
t.reltuples record_count,
CASE t.reldistmethod
WHEN 6003 THEN ‘CENTRAL_HOST’
WHEN 6005 THEN ‘RANDOM’
WHEN 6004 THEN ‘ATTRIBUTES(‘||
RTRIM(NVL(d1.attname||’,’, ”)||
NVL(d2.attname||’,’, ”)||
NVL(d3.attname||’,’, ”)||
NVL(d4.attname||’,’, ”)||
NVL(d5.attname||’,’, ”)||
NVL(d5.attname, ”),
‘,’
)||’)’
ELSE ‘UNKNOWN’
END reldistmethodname
FROM _v_sys_object_data sod
INNER JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
INNER JOIN _v_sys_database d ON sod.dbname = d.objname
LEFT JOIN _t_class t ON sod.objid = t.oid
LEFT JOIN _v_table_dist d1 ON sod.objid = d1.relid
LEFT JOIN _v_table_dist d2 ON d1.relid = d2.relid AND d1.distseqno = d2.distseqno+1
LEFT JOIN _v_table_dist d3 ON d2.relid = d3.relid AND d2.distseqno = d3.distseqno+1
LEFT JOIN _v_table_dist d4 ON d3.relid = d4.relid AND d3.distseqno = d4.distseqno+1
LEFT JOIN _v_table_dist d5 ON d4.relid = d5.relid AND d4.distseqno = d5.distseqno+1
WHERE sod.dbname = CURRENT_CATALOG
ORDER BY soss.skew DESC
LIMIT 20

–Skew winners
SELECT sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.createdate,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_space_in_gbytes,
soss.used_bytes/1024/1024 used_space_in_mbytes,
soss.skew,
SUM(soss.skew)OVER(PARTITION BY sod.dbname, sod.owner) skew_sum_by_db_and_user
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MVIEW_STORE’)
AND sod.dbname = ‘KROGER_DVIS_UTILS’
ORDER BY sod.dbname, skew_sum_by_db_and_user DESC, sod.owner, soss.skew DESC

SELECT sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.createdate,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_gbytes,
soss.skew,
SUM(soss.used_bytes)OVER(PARTITION BY sod.dbname, sod.owner)/1024/1024/1024 used_gbytes_by_db_and_user,
SUM(soss.used_bytes)OVER(PARTITION BY sod.dbname, sod.owner) used_bytes_by_db_and_user
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MVIEW_STORE’)
AND sod.dbname = ‘KROGER_DVIS_UTILS’
ORDER BY sod.dbname, used_bytes_by_db_and_user DESC, sod.owner, soss.used_bytes DESC;
SELECT sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.createdate,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_space_in_gbytes,
soss.used_bytes/1024/1024 used_space_in_mbytes,
soss.skew,
SUM(soss.skew)OVER(PARTITION BY sod.dbname, sod.owner) skew_sum_by_db_and_user
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MVIEW_STORE’)
AND sod.dbname = ‘KROGER_DVIS_UTILS’
ORDER BY sod.dbname, skew_sum_by_db_and_user DESC, sod.owner, soss.skew DESC
SELECT sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.createdate,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_space_in_gbytes,
soss.used_bytes/1024/1024 used_space_in_mbytes,
soss.skew,
SUM(soss.skew)OVER(PARTITION BY sod.dbname) skew_sum_by_db,
SUM(soss.skew)OVER(PARTITION BY sod.owner) skew_sum_by_user
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MVIEW_STORE’)
AND soss.skew > 0
ORDER BY skew_sum_by_db DESC, sod.dbname, soss.skew DESC;

SELECT sod.dbname,
sod.owner objownerusername,
sod.objname,
sod.createdate,
sod.objtype,
soss.used_bytes/1024/1024/1024 used_space_in_gbytes,
soss.used_bytes/1024/1024 used_space_in_mbytes,
soss.skew,
SUM(soss.skew)OVER(PARTITION BY sod.dbname) skew_sum_by_db,
SUM(soss.skew)OVER(PARTITION BY sod.owner) skew_sum_by_user
FROM _v_sys_database d
LEFT JOIN _v_sys_object_data sod ON d.objname = sod.dbname
LEFT JOIN _v_sys_object_storage_size soss ON sod.objid = soss.tblid
WHERE sod.objtype IN (‘TABLE’, ‘MVIEW_STORE’)
AND soss.skew > 0
ORDER BY skew_sum_by_user DESC, sod.owner, soss.skew DESC;