PPAS , Oracle partition table 조회
# ORACLE
select SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE from dba_segments where owner='KING' and SEGMENT_TYPE='TABLE PARTITION';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
----------------------- ------------------------- ------------------------
KING_LOG PT_LOG_20140101 TABLE PARTITION
KING_LOG PT_LOG_20140201 TABLE PARTITION
KING_LOG PT_LOG_20140301 TABLE PARTITION
KING_LOG PT_LOG_20140401 TABLE PARTITION
# PPAS
select c.relname "partition table",h.inhparent::regclass "parent table" from pg_class c inner join pg_inherits h on(c.oid=h.inhrelid);
partition table | parent table
------------------------------------------+-----------------
king_log_pt_king_log_20140101 | king_log
king_log_pt_king_log_20140201 | king_log
king_log_pt_king_log_20140301 | king_log
king_log_pt_king_log_20140401 | king_log
# PPAS partition table size 조회
select inhparent::regclass as parent_table,pg_size_pretty(sum(pg_relation_size(inhrelid::regclass))) as partition_table_size
from pg_inherits
group by inhparent;
#ppas partition index size 조회 (PPAS는 global index가 없기에 각 partition에 인덱스 생성)
select inhparent::regclass as "partition table",pg_size_pretty(sum(pg_relation_size(i.indexrelid::regclass))) as "partition index size"
from pg_index i inner join pg_inherits h on(i.indrelid=h.inhrelid)
group by inhparent;
select inhparent::regclass as "partition table",pg_size_pretty(sum(pg_relation_size(i.indexrelid::regclass))) as "partition index size"
from pg_index i inner join pg_inherits h on(i.indrelid=h.inhrelid)
group by inhparent;
위 쿼리로 해당 partition table의 전체 사이즈를 편하게 조회 가능합니다.
