Finding distribution keys of all tables in the greenplum database

posted Jul 25, 2014, 4:59 AM by Sachchida Ojha   [ updated Jul 23, 2018, 12:01 PM ]
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;















Comments