특정 Column 통계정보갱신가이드 유니원아이앤씨 DB 사업부이대혁 2015 년 03 월 02 일 문서정보프로젝트명서브시스템명 버전 1.0 문서명 특정 Column 통계정보갱신가이드 작성일 2015-03-02 작성자 DB사업부이대혁사원 최종수정일 2015-03-02 문서번호 UNIONE-201503021500-LDH 재개정이력 일자내용수정인버전 문서배포이력 발신자수신자배포목적일자비고 유니원아이앤씨 특정 Column 통계정보갱신가이드 2015-03-02.
Table of Contents 1 개요... 3 2 테스트계정접속... 3 2.1 테스트계정접속... 3 2.2 테스트용테이블생성... 4 2.3 생성된테이블에데이터생성... 5 2.4 생성된테이블통계정보확인... 6 2.5 TEST 테이블통계정보생성... 7 2.6 새로운컬럼추가... 7 2.7 추가된컬럼에데이터값입력... 8 3 추가된컬럼통계정보생성... 9 3.1 ID3 Column만통계정보생성... 9 4 참고자료... 10 APPLIES TO:... 11 GOAL... 11 SOLUTION... 11 2/13
1 개요 특정컬럼에대해서만통계정보를갱신하는방법을소개하는문서입니다. 테스트에서는테이블에데이터를생성후특정컬럼을추가한뒤해당컬럼만갱신하여확인합니다. 2 테스트계정접속 테스트계정은 Scott 유져로진행하도록하겠습니다. 2.1 테스트계정접속 SQL> conn scott/tiger Connected. SQL> SQL> select * from tab; TNAME TABTYPE CLUSTERID -------------------------------------------------------------------------- -------------- ---------- DEPT EMP BONUS SALGRADE DUMMY 3/13
2.2 테스트용테이블생성 SQL> SQL> CREATE test 2 ( id1 NUMBER(20) 3, id2 NUMBER(20) 4, cont VARCHAR2(20) 5 ); Table created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------ -------------- ---------- DEPT EMP BONUS SALGRADE DUMMY TEST TEST 테이블을생성합니다. 4/13
컬럼은 id1, id2, cont 3 개의컬럼을 Varchar2 형태로생성하였습니다. 2.3 생성된테이블에데이터생성 SQL> SQL> DECLARE x NUMBER := 100; BEGIN FOR i IN 0..80100 LOOP IF MOD(i,2) = 0 THEN -- i is even INSERT INTO test VALUES (i, x, 'i is even'); ELSE INSERT INTO test VALUES (i, x, 'i is odd'); END IF; x := x + 100; END LOOP; COMMIT; END; / PL/SQL procedure successfully completed. procedure 를이용하여데이터 80100 건입력 5/13
2.4 생성된테이블통계정보확인 SQL> set lines 200 SQL> column COLUMN_NAME format a5 heading COL SQL> column NUM_DISTINCT format 99990 SQL> column LOW_VALUE format 99990 SQL> column HIGH_VALUE format 99990 SQL> column NUM_BUCKETS format 99990 SQL> column SAMPLE_SIZE format 99990 SQL> col LOW_VALUE for a20 SQL> col HIGH_VALUE for a20 SQL> SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS, 2 NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM 3 from user_tab_columns 4 where table_name = 'TEST' ; COL NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE HISTOGRAM -------- --------------------- -------------------- ------------------- -------------- ------------------- ---------------------- ------------------------- ----------------- -------------------------- ID1 ID2 CONT NONE NONE NONE 테이블생성후데이터값을입력하였으나통계정보와 HISTOGRAM 없음을확인할수있습니다. 6/13
2.5 TEST 테이블통계정보생성 SQL> exec DBMS_STATS.GATHER STATS (NULL,'TEST', method_opt => 'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. 통계정보생성완료 SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS, 2 NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM 3 from user_tab_columns 4 where table_name = 'TEST' 5 / COL NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE HISTOGRAM ---------- -------------------------- ------------------------------------- ----------------------- ----------------- -------------------- -------------------------- ---------------------------- --------------------- ------------------------- ID1 80101 C106 C3090161.000012484 0 1 2015-02-07:01:34:49 80101 NONE ID2 80101 C207 C4090162.000012484 0 1 2015-02-07:01:34:49 80101 NONE CONT 2 69206973206576656E 69206973206F6464 5 0 1 2015-02-07:01:34:49 5482 NONE 통계정보생성후재확인결과통계정보와 HISTOGRAM 이생성된것을확인할수있습니다. 2.6 새로운컬럼추가 현재 TEST 테이블은 ID1, ID2, CONT 컬럼밖에존재하지않으며새로운컬럼 ID3 을추가합니다. SQL> alter table test add (id3 char(1)); 7/13
Table altered. SQL> desc test Name Null Type ------------------------ ------------ ------------------------------- ID1 NUMBER(20) ID2 NUMBER(20) CONT VARCHAR2(20) ID3 CHAR(1) ID3 컬럼이추가되었음을확인가능합니다. 2.7 추가된컬럼에데이터값입력 추가된 ID3 컬럼의데이터를입력후통계정보를확인합니다. SQL> update test set id3='y' where id1<=50000; 50001 rows updated. SQL> update test set id3='n' where id1>50000; 30100 rows updated. SQL> commit; Commit complete. SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS, 8/13
2 NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM 3 from user_tab_columns 4 where table_name = 'TEST' 5 / COL NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE HISTOGRAM --------- -------------------------- ---------------------------- ------------------------------ ----------------- --------------------- --------------------------- ---------------------------- ----------------------- ------------------------ ID1 80101 C106 C3090161.000012484 0 1 2015-02-07:01:34:49 80101 NONE ID2 80101 C207 C4090162.000012484 0 1 2015-02-07:01:34:49 80101 NONE CONT 2 69206973206576656E 69206973206F6464.5 0 1 2015-02-07:01:34:49 5482 NONE ID3 NONE ID3 컬럼에만여전히통계정보가없음을확인하실수있습니다. 3 추가된컬럼통계정보생성 3.1 ID3 Column 만통계정보생성 SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name>') Syntax 구문은위와같습니다. SQL> exec dbms_stats.gather_table_stats(null, 'TEST', cascade=>false, method_opt=>'for columns ID3'); PL/SQL procedure successfully completed. 9/13
통계정보확인 SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS, 2 NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM 3 from user_tab_columns 4 where table_name = 'TEST' 5 / COL NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE HISTOGRAM --------- -------------------------------- ---------------------------- --------------------------- ---------------- ---------------------- --------------------------- ---------------------------- ------------------------ ------------------ ID1 80101 C106 C3090161.000012484 0 1 2015-02-07:01:34:49 80101 NONE ID2 80101 C207 C4090162.000012484 0 1 2015-02-07:01:34:49 80101 NONE CONT 2 69206973206576656E 69206973206F6464.5 0 1 2015-02-07:01:34:49 5482 NONE ID3 0 0 80101 0 2015-02-07:01:56:18 NONE 통계정보의 LAST_ANALYZED 시간확인시 ID3 컬럼의통계정보만갱신됨을확인 4 참고자료 Metalink 참조문서 How To Quickly Add/Remove Column Statistics (Histograms) For A Column ( 문서 ID 390249.1) In this Document 10/13
Goal Solution APPLIES TO: Oracle Database - Enterprise Edition - Version 8.1.7.4 and later PeopleSoft Enterprise PT PeopleTools - Version 8.54 to 8.54 [Release 8.4] Information in this document applies to any platform. GOAL In some situations it is necessary to quickly add or remove histograms from a particular column (also known as column statistics.) This article shows how to accomplish this with one command. The commands given in this article will not affect column statistics currently gathered for other columns, they will only touch the specified column. Column statistics can be: - so called minimal consisting of just 1 bucket (2 endpoints) with min/max value information required by the Optimizer - histograms with between 2 and 254 buckets (3 and 255 endpoints respectively) - absent with no column information recorded in the data dictionary (not normally recommended) SOLUTION Assuming you are connected as the owner of the table. To add column statistics use one of the following varieties: using up to the default number of buckets 75: SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, meth od_opt=>'for columns <column_name>'); 11/13
using the appropriate number of buckets for the data distribution (will be minimal when not skewed) : SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, meth od_opt=>'for columns <column_name> size skewonly'); using maximum number of buckets (up to 254) : SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, meth od_opt=>'for columns <column_name> size 254'); using up to specified number of buckets N (N is between 2 and 254) : SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, meth od_opt=>'for columns <column_name> size N'); gathering statistics for multiple columns with different bucket sizes : SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>'); gathering statistics for multiple columns with different bucket sizes plus minimal information for the remaining columns: SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, meth od_opt => 'for all columns size 1 for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>'); To delete histograms from a column leaving behind base column statistics i.e. minimal information required for the Optimizer (this is for pre-oracle11g versions:) SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt= >'for columns <column_name> size 1'); To completely erase all types of column statistics/histograms for a column including minimal statistics: 12/13
SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>'); This is generally not recommended as the Optimizer needs at least the minimal (size 1) information. In Oracle11g there is new syntax to delete the histogram for a column while still leaving behind the base column statistics: SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>', col_stat_type=>'histogram'); Additionally there is functionality to delete histograms for a partition: exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>', colname=>'<column_name>',partname=>'<partition_name>', col_stat_type=>'histogram') and to delete column histograms for the table and all its partitions: exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>', colname=>'<column_name>',cascade_parts=>true,col_stat_type=>'histogram') 13/13