We have migrated to new Google sites!
Post date: Jul 25, 2014 11:59:56 AM
SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER BY pgn.nspname,
pgc.relname;
Preserve the order in which distribution key is defines. View shows n row for table having composite (n columns) distribution key.
create view dbadmin.v_distribution_key as
SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey,distrokey.attnum
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER by pgn.nspname,pgc.relname,distrokey.attnum ;
Preserve the order in which distribution key is defineds. View shows 1 row for table having composite (n columns) distribution key.
create view dbadmin.v_distribution_key3
as
select
a.schemaname,
a. tablename,
max(pgc.relpages) as relpages,
max(pgc.reltuples) as reltuples
, (select array_to_string(ARRAY(select distributionkey from
(
SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey,distrokey.attnum
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER by pgn.nspname,pgc.relname,distrokey.attnum
) as dkey where schemaname=a.schemaname
and tablename=a.tablename),', ') )as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid
and a.schemaname=pgn.nspname
group by a.schemaname,a.tablename;
create view dbadmin.v_distribution_key2
as
select
a.schemaname,
a. tablename,
max(pgc.relpages) as relpages,
max(pgc.reltuples) as reltuples
, (select array_to_string(ARRAY(select distributionkey from dbadmin.v_distribution_key where schemaname=a.schemaname
and tablename=a.tablename),', ') )as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid
and a.schemaname=pgn.nspname
group by a.schemaname,a.tablename;
create view dbadmin.v_distribution_key2
as
select
a.schemaname,
a. tablename,
max(pgc.relpages) as relpages,
max(pgc.reltuples) as reltuples
, (select ARRAY(select distributionkey from dbadmin.v_distribution_key where schemaname=a.schemaname
and tablename=a.tablename))as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid
and a.schemaname=pgn.nspname
group by a.schemaname,a.tablename;
select distinct
schemaname,
tablename,
threshold,
relpages,
reltuples,
count(distributionkey) over (partition by schemaname,tablename) as noofcolindistkey,
array_to_string(array_agg(distributionkey) over (partition by schemaname,tablename),',') as distkey
from
(
SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey,distrokey.attnum,
'72 hours' as threshold,
pgc.relpages,
pgc.reltuples
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER by pgn.nspname,pgc.relname,distrokey.attnum)
as dkey;