This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find a list of distribution keys of a database. In the Greenplum MPP architecture, distribution keys are playing a primary role in selecting data.
If we define proper distribution key, we don’t require even table indexes.
‘ Using below script, Greenplum DBA can get the list of all distribution keys which further they can use for ad-hoc database reporting as well.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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; |
Leave a Reply