가족사랑 & 부자인생: 원하는 파티션의 통계정보 import 하기 [ DBMS_STATS.COPY_TABLE

#####################################
# 원하는 파티션의 통계정보 import 하기
# DBMS_STATS.COPY_TABLE_STATS
#####################################

10.2.0.4 부터 지원되는 Package 입니다.
11G R2 에서 테스트 결과 아주 잘 되는것을 확인 되었습니다.
위의 패키지가 지원되기 전에는 아주 불편했던 기억이 나네요.
이 것은 이제 그만 머리속에서 지워보도록 하겠습니다. ㅋㅋㅋ

-- 1. Range 파티션 생성
drop table T1 purge;
CREATE TABLE T1(C1, C2)
PARTITION BY RANGE (C1) (
PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (2000),
PARTITION P3 VALUES LESS THAN (3000)
)
AS
SELECT LEVEL-1, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3000
;

commit;

-- 2. 데이타 확인
select * from T1;

-- 3. 통계정보 수행
--exec dbms_stats.gather_table_stats(user, 't1', granularity=>'all');
exec dbms_stats.gather_table_stats('SHWOO', 't1', granularity=>'all');

-- 4. 통계정보 확인
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME ='T1';
/**
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- -------------------
SHWOO T1 3000 2010-03-18 18:18:46

통계정보 수행전까지는 NUM_ROWS 와 LAST_ANALYZED 값은 없을 것입니다.
**/

SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';

/**

TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ------------ --------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06

**/

-- 5. 파티션 ADD 및 데이타 입력
alter table t1
add partition p4 values less than (4000)
;

insert into t1
select level+3000-1, level
from dual
connect by level <= 1000
;
COMMIT;

-- 6. 통계정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';

/**

   TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
   ---------------- ------------- ----------------- ----------- -------------------
   SHWOO T1 P1 1000 2010-03-18 17:51:05
   SHWOO T1 P2 1000 2010-03-18 17:51:06
   SHWOO T1 P3 1000 2010-03-18 17:51:06
   SHWOO T1 P4

**/

-- 7. 해당 파티션만 통계정보 수행
exec dbms_stats.gather_table_stats('SHWOO', 't1', 'p4');

-- 8. 통계정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';

/**

   TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
   ----------------- ------------- ---------------- ----------- -------------------
   SHWOO T1 P1 1000 2010-03-18 17:51:05
   SHWOO T1 P2 1000 2010-03-18 17:51:06
   SHWOO T1 P3 1000 2010-03-18 17:51:06
   SHWOO T1 P4 1000 2010-03-18 17:57:37

   위의 방법은 데이타가 존재 하지 않을시에는 NUM_ROWS가 0 값 입니다.
   한마디로 데이타가 존재시에만 효과가 있겠네요.

**/

-- 9. 파티션 ADD
alter table t1
  add partition p5 values less than (5000)
;

-- 10. 해당 파티션 정보 확인
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';

/**

   TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
   ---------------- -------------- ----------------- ----------- -------------------
   SHWOO T1 P1 1000 2010-03-18 17:51:05
   SHWOO T1 P2 1000 2010-03-18 17:51:06
   SHWOO T1 P3 1000 2010-03-18 17:51:06
   SHWOO T1 P4 1000 2010-03-18 17:57:37
   SHWOO T1 P5

**/

-- 11. 원하는 파티션의 통계정보 데이타 넣기
exec dbms_stats.copy_table_stats('SHWOO', 't1', srcpartname=>'p4', dstpartname=>'p5');
-- 해당 srcpartname 의 값은 P1 ~ P4 까지 아무거나 넣으시면 됩니다.
-- dstpartname 의 값은 srcpartname 의 통계정보를 그대로 copy를 해 줍니다.

-- 12. 통계정보 확인 하기
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T1';

/**

TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
-------------- ------------- ----------------- ----------- -------------------
SHWOO T1 P1 1000 2010-03-18 17:51:05
SHWOO T1 P2 1000 2010-03-18 17:51:06
SHWOO T1 P3 1000 2010-03-18 17:51:06
SHWOO T1 P4 1000 2010-03-18 17:57:37
SHWOO T1 P5 1000 2010-03-18 17:57:37

NUM_ROWS 가 기존 P4 의 파티션 정보를 그대로 copy를 해 주었네요.
PLAN 이 변경되지 않겠네요.

**/

-- 13. 원하는 통계정보 삭제하기
exec dbms_stats.delete_table_stats ( 'SHWOO', 'T1', 'P5');

########### 참고 내용 ################################################
11g 에서부터는 아래 버그를 잘 처리 되었다고 하네요.
버그 내용은 이러 합니다. LOW_VALUE , HIGH_VALUE 의 값이 COPY를 하게 되면
원본 그대로 변경 없이 승계를 받는 것이라고 하네요.
예로 P3을 P4로 COPY 시 LOW_VALUE : 2000 , HIGH_VALUE:2999 의 값이
P4에 LOW_VALUE : 3000 , HIGH_VALUE:3999 로 세팅 되는것이 아니라
LOW_VALUE : 2000 , HIGH_VALUE:2999 로 세팅이 됩니다.

SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P3';

TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINC LOW_VALUE HIGH_VALUE
------------- --------------- ---------------- ----------- ----------- -----------
T1 P3 C1 1000 2000 2999
T1 P3 C2 1000 2001 3000

SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P4';

TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINC LOW_VALUE HIGH_VALUE

------------- --------------- ---------------- ----------- ----------- -----------

T1 P4 C1 1000 3000 3999

T1 P4 C2 1000 1 1000

SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,

UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,

UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE

FROM DBA_PART_COL_STATISTICS

WHERE OWNER = 'SHWOO' AND TABLE_NAME = 'T1' AND PARTITION_NAME = 'P5';

TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINC LOW_VALUE HIGH_VALUE

------------- --------------- ---------------- ----------- ----------- -----------

T1 P5 C1 1000 4000 5000

T1 P5 C2 1000 1 1000

10G 이하에서는 아래 처럼 처리 하면 된다고 하네요.

1. 샘플 입니다..

10g에서는 어쩔 수 없이 수작업으로 보정하는 수 밖엔 없네요...

다음의 파티션 기준 컬럼에 대한 예제 스크립트입니다.

DECLARE
V_SREC DBMS_STATS.STATREC;
V_ArrVals DBMS_STATS.CHARARRAY;
BEGIN

V_SREC.epc := 2;
V_SREC.eavs := null;
V_SREC.bkvals := DBMS_STATS.NUMARRAY(2.60592219238428E35, 2.60592219238428E35);
V_ArrVals := DBMS_STATS.CHARARRAY('20090401', '20090430');

DBMS_STATS.PREPARE_COLUMN_VALUES(V_SREC, V_ArrVals);

DBMS_STATS.SET_COLUMN_STATS
(
OWNNAME=>'owner명',
TABNAME=>'테이블명',
COLNAME=>'SALEDATE',
PARTNAME=>'P200904',
DISTCNT=>30,
DENSITY=>0.0357142857142857,
NULLCNT=>0,
AVGCLEN=>9,
SREC=>V_SREC
);
END;
/

2. 패치
버그번호는 8719831

출처 : http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/

가족사랑 & 부자인생

2010년 3월 18일 목요일

원하는 파티션의 통계정보 import 하기 [ DBMS_STATS.COPY_TABLE_STATS ]

댓글 없음:

댓글 쓰기

프로필

블로그 보관함

팔로어