GP - Script to find all Distribution Keys
Distribution keys play an important role in selecting data from table.
SELECT
pgn.nspname as table_owner
,pgc.relname as table_name
,COALESCE(pga.attname,'DISTRIBUTED RANDOMLY') as distribution_keys
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;
SELECT
pgn.nspname as table_owner
,pgc.relname as table_name
,COALESCE(pga.attname,'DISTRIBUTED RANDOMLY') as distribution_keys
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;
Comments
Post a Comment