GREENP LUM常用SQL语句
查看对象大小(表、索引、数据库等)
s elect pg_s ize_p retty(pg_r elati on_si ze('$schem a.$ta ble'));
查看用户(非系统)表和索引
selec t * f rom p g_sta t_use r_tab les;
see ct *frompg_st at_us er_in dexes;
查看表分区
selec t b.n spnam e||'.'||a.relna me as tabl ename, d.p arnam e aspartn ame
frompg_cl ass a, pg_names paceb, pg_part ition c, p g_par titio n_rul e d
where a.re lname space = b.oid
and b.nspn ame = '$sc hema'
and a.re lname = '$table'
an d a.o id =c.par relid
and c.oi d = d.paro id
o rderby pa rname;
查看Dist ribut ed ke y
se lect b.at tname
fro m pg_class a, p g_att ribut e b,pg_ty pe c, gp_d istri butio n_pol icy d, pg_name space e w hered.loc aloid = a.oid
and a.reln amesp ace = e.oi d
an d e.n spnam e = '$sche ma'
and a.reln ame='$tabl e'
a nd a.oid = b.at treli d
an d b.a tttyp id =c.oid
and b.at tnum> 0
and b.attn um =any(d.attr nums)
ord er by attn um;
查看当前存活的查询
sel ect p rocpi d aspid,sess_id as sess ion,usena me as user, cur rent_query as q uery,
wait ing,
date_trun c('se cond', que ry_st art)as st art_t ime,clien t_add r asusera ddr
frompg_st at_ac tivit y
wh ere d atnam e ='$PGDAT ABASE'
an d cur rent_query notlike'%fro m pg_stat_activ ity%w heredatna me =%'
or der b y sta rt_ti me;
表上被用作外键的字段名
s elect f.co nname, pg_get_c onstr aintd ef(f.oid), t2.r elnam e
fr om pg_clas s t,pg_cl ass t2, pg_cons train t f
where f.co nfrel id =t.oid
and f.co nreli d = t2.oid
and f.co ntype = 'f'
an d t.r elnam e = '$tabl e';
1. 查看表使用空间
SEL ECT *
F ROM PUB LIC.d ba_se gment s
WHEREo wner LIK E 'owber_name'
A ND t able_name LIK E '%tabl e_nam e%'
ORDE R B Y t able_name;
2. GP中查看分区：
se lect par titio nname,part ition bound ary from pg_part ition s w here
sch emana me='s chema name' andtable name='tabl e_nam e';
sel ect part ition name,parti tionb ounda ry from pg_catal og.pg_part ition s w heresche manam e='sc heman ame'table name='tabl e_nam e';
3.查看正在运行的sql
se lect * from pg_stat_acti vity;
4. 修改表的own er语句
Alt er t able tabl e_nam e own er to owne r_nam e;
5.增加表分区
AL TER T ABLEtable_name AD D PAR TITIO N P20091001 ST ART (DATE'2009-10-01') I NCLUS IVE E ND (D ATE '2009-10-
02') E XCLUS IVE W ITH(a ppend only=true,compr essle vel=5);
6.修改列类型
greenplum数据库AL TER T ABLEtable_name ALT ER CO LUMNa TYP E var char(2048);
7. 修改dist ribut ed 列
alt er ta ble t able_name setdistr ibute d by(colum n_1);
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论