[Oracle] 파티션 테이블 통계 복사

오라클 파티션 테이블 통계정보 Copy & Paste 시 참고

 

-- ########################################
-- 파티션 테이블 통계 복사
-- ########################################
-- 파티션 통계 확인 (TABLE)
SELECT OWNER,TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED 
  FROM DBA_TAB_STATISTICS 
 WHERE TABLE_NAME='T1' 
   AND PARTITION_NAME IN ('T1202105','T1202106')
;
-- 파티션 통계 확인 (INDEX)
SELECT * FROM DBA_INDEXES WHERE TABLE_NAME='T1';
SELECT OWNER,TABLE_NAME,INDEX_NAME,PARTITION_NAME, LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED
  FROM DBA_IND_STATISTICS
 WHERE INDEX_NAME='PK_T1' 
   AND PARTITION_NAME IN ('T1202105','T1202106')
;
-- 파티션 통계정보 복사
BEGIN  DBMS_STATS.COPY_TABLE_STATS('ORAKIM', 'T1', SRCPARTNAME=>'T1202105', DSTPARTNAME=>'T1202106');END;
BEGIN  DBMS_STATS.COPY_TABLE_STATS('ORAKIM', 'T1', SRCPARTNAME=>'T1202106', DSTPARTNAME=>'T1202107');END;
BEGIN  DBMS_STATS.COPY_TABLE_STATS('ORAKIM', 'T1', SRCPARTNAME=>'T1202107', DSTPARTNAME=>'T1202108');END;