GreenPlum DBA常用SQL

来源:未知 责任编辑:责任编辑 发表时间:2015-09-09 15:36 点击:
GreenPlum DBA常用SQL
 
查看对象大小(表、索引、数据库等)
select pg_size_pretty(pg_relation_size(’$schema.$table’));
 
查看用户(非系统)表和索引
select * from pg_stat_user_tables;
 
seect * from pg_stat_user_indexes;
  www.2cto.com  
查看表分区
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;
  www.2cto.com  
查看当前存活的查询
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’;
  www.2cto.com  
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;
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
用户名: 验证码:点击我更换图片
最新评论 更多>>

推荐热点

  • Request.ServerVariables 参数大全
  • 查看sql修改痕迹(SQL Change Tracking on Table)
  • 写给MongoDB开发者的50条建议Tip1
  • Percolator与分布式事务思考(二)
  • App数据层设计及云存储使用指南
  • PostgreSQL启动过程中的那些事三:加载GUC参数
  • SQL Server、Oracle、db2所提供的简装版(Express)比较
  • PostgreSQL 安装问题
  • 【自主研发-贡献给SQL Server人员】索引诊断与优化软件使用说明
网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
Copyright © 2008-2015 计算机技术学习交流网. 版权所有

豫ICP备11007008号-1