by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Manual Histogram




Histogram을 생성하지 않고 조작하기

drop table t1 purge;
create table t1(c1 int);

select table_name,column_name,num_distinct,low_value,high_value,histogram
 from dba_tab_columns where table_name = 'T1';

TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE HISTOGRAM
---------- ----------- ------------ --------- ---------- ---------
T1         C1                                            NONE

Histogram을 수동으로 생성하는 pl/sql을 작성

declare
    v_srec    dbms_stats.statrec;
    v_numvals dbms_stats.numarray;
  begin
    v_srec.epc := 5;
    v_srec.eavs := null;
    v_numvals := dbms_stats.numarray(1, 2, 3, 4, 5);
    v_srec.bkvals := dbms_stats.numarray(10000, 20000, 30000, 40000, 50000);
    dbms_stats.prepare_column_values(v_srec, v_numvals);

    dbms_stats.set_table_stats(
      ownname=>user,
      tabname=>'t1',
      numrows=>150000,
      numblks=>1000,
      avgrlen=>150
    );

    dbms_stats.set_column_stats(
      ownname=>user,
      tabname=>'t1',
      colname=>'c1',
      distcnt=>5,
      density=>1/2/150000,
      nullcnt=>0,
      srec=>v_srec
    );
  end;
  /

select table_name,column_name,num_distinct,low_value,high_value,histogram
 from dba_tab_columns where table_name = 'T1';


TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE HISTOGRAM
---------- ----------- ------------ --------- ---------- ---------
T1         C1                     5 C102      C106       FREQUENCY

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.