GreenPlum DBA常用SQL

檢視物件大小(表、索引、資料庫等)
select pg_size_pretty(pg_relation_size(’$schema.$table’));

檢視使用者(非系統)表和索引
select * from pg_stat_user_tables;

seect * from pg_stat_user_indexes;

檢視錶分割槽
select b.nspname||’.'||a.relname as tablename, d.parname as partname
from pg_class a, pg_namespace b, pg_partition c, pg_partition_rule d
where a.relnamespace = b.oid
and b.nspname = ‘$schema’
and a.relname = ‘$table’
and a.oid = c.parrelid
and c.oid = d.paroid
order by parname;

檢視Distributed key
select  b.attname
from pg_class a, pg_attribute b, pg_type c, gp_distribution_policy  d, pg_namespace e
where d.localoid = a.oid
and a.relnamespace = e.oid
and e.nspname = ‘$schema’
and a.relname=’$table’
and a.oid = b.attrelid
and b.atttypid = c.oid
and b.attnum > 0
and b.attnum = any(d.attrnums)
order by attnum;

檢視當前存活的查詢
select procpid as pid, sess_id as session, usename as user, current_query as query, waiting,
date_trunc(’second’, query_start) as start_time, client_addr as useraddr
from pg_stat_activity
where datname =’$PGDATABASE’
and current_query not like ‘%from pg_stat_activity%where datname =%’
order by start_time;

表上被用作外來鍵的欄位名
select f.conname, pg_get_constraintdef(f.oid), t2.relname
from pg_class t, pg_class t2, pg_constraint f
where f.confrelid = t.oid
and f.conrelid = t2.oid
and f.contype = ‘f’
and t.relname = ‘$table’;

 

1. 檢視錶使用空間
 SELECT      *  
FROM         PUBLIC.dba_segments  
WHERE         owner   LIKE   'owber_name'
 AND            table_name   LIKE   '%table_name%'
 ORDER   BY   table_name;
 
2. GP中檢視分割槽：
 select   partitionname,partitionboundary   from   pg_partitions   where   tablename='table_name';  
select   partitionname,partitionboundary   from   pg_catalog.pg_partitions   where   tablename='table_name';
 
3.  檢視正在執行的sql
   select   *   from   pg_stat_activity;
 
4. 修改表的owner語句
 Alter table table_name owner to owner_name;
 
5. 增加表分割槽
 ALTER TABLE table_name   ADD PARTITION P20091001  START (DATE '2009-10-01') INCLUSIVE END (DATE '2009-10-02')  EXCLUSIVE WITH(appendonly=true,compresslevel=5);
 
6. 修改列型別
 ALTER TABLE table_name  ALTER COLUMN a TYPE varchar(2048);
 
7. 修改distributed 列
 alter table table_name  set distributed by(column_1);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/177006/viewspace-719186/，如需轉載，請註明出處，否則將追究法律責任。