설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 55건, 최근 0 건
   

파티션 Table Size 확인

글쓴이 : 주킹 날짜 : 2015-01-23 (금) 11:09 조회 : 2695
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의 전체 사이즈를 편하게 조회 가능합니다.

미디칭구 2015-11-06 (금) 17:54
파티션테이블 지원이 멋지네요~^^
댓글주소
   

postgresdba.com