greenplum分区表查看所占空间⼤⼩
在使⽤greenplum数据库的时候，有的时候想要查看表所占⽤空间的⼤⼩，会使⽤如下⼆个函数pg_relation_size和pg_size_pretty.
前者⽤来查看数据⼤⼩，后者是human readable的调整.⽅法如下:
select pg_size_pretty(pg_relation_size('relation_name')) ;
select pg_size_pretty(pg_relation_size(oid)) ;
但是，对于分区表⽽⾔，这个⽅法就没有⽤了，会发现使⽤后表的⼤⼩为0bytes.原因在于:GP的分区表的主表只是⼀个表定义，其实际数据内容存储在继承⽗表的分区⼦表⾥⾯了.上⽹查了⼀下，没发现有相应的函数,⼲脆写了个函数来实现.
-- Function: calc_partition_table(character varying, character varying)
-- DROP FUNCTION calc_partition_table(character varying, character varying);
CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname character varying, v_tablename character varying) RETURNS bigint AS
$BODY$
DECLAREgreenplum数据库
v_calc BIGINT :=0;
v_total BIGINT :=0;
v_tbname VARCHAR(200);
cur_tbname cursor for select schemaname||'.'||partitiontablename as tb from pg_partitions
where schemaname=v_schemaname and tablename=v_tablename;
BEGIN
OPEN cur_tbname;
loop
FETCH cur_tbname into v_tbname;
if not found THEN
exit;
end if;
EXECUTE'select pg_relation_size('''||v_tbname||''')'into v_calc;
v_total:=v_total+v_calc;
end loop;
CLOSE cur_tbname;
RETURN v_total;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION calc_partition_table(character varying, character varying) OWNER TO gpadmin;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。