通过dbms_stats包修改统计信息里的最大值最小值，等频等高直方图-CSDN博客

--------------------------------------------------------------------
创建测试表
--------------------------------------------------------------------
create table TEST ( a number, b varchar2(128), c date );
insert into TEST select object_id, object_name, created from user_objects where rownum < 1000;
commit;

--------------------------------------------------------------------
                 分析统计信息
--------------------------------------------------------------------
begin
dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'test',
                                no_invalidate    => FALSE,
                                estimate_percent => null,
                                force            => true,
                                method_opt       => 'for all columns size 1',
                                cascade          => true);
end;
/

--------------------------------------------------------------------
                设置字段最大值最小值
--------------------------------------------------------------------
DECLARE
   srec               DBMS_STATS.STATREC;
   v_distcnt          NUMBER;
   v_density          NUMBER;
   v_nullcnt          NUMBER;
   v_avgclen          NUMBER;
   numvals            DBMS_STATS.NUMARRAY;
   charvals           DBMS_STATS.CHARARRAY;
   datevals           DBMS_STATS.DATEARRAY;

BEGIN

   -- 数值类型

   DBMS_STATS.get_column_stats (ownname      => user,
                                tabname      => 'TEST',
                                colname      => 'A',
                                distcnt      => v_distcnt,
                                density      => v_density,
                                nullcnt      => v_nullcnt,
                                srec         => srec,
                                avgclen      => v_avgclen
                               );


   numvals := DBMS_STATS.numarray (1, 100000);

   DBMS_STATS.prepare_column_values (srec, numvals);

   DBMS_STATS.set_column_stats (ownname      => user,
                                tabname      => 'TEST',
                                colname      => 'A',
                                distcnt      => v_distcnt,
                                density      => v_density,
                                nullcnt      => v_nullcnt,
                                srec         => srec,
                                avgclen      => v_avgclen
                               );

   -- 字符类型

   DBMS_STATS.get_column_stats (ownname      => user,
                                tabname      => 'TEST',
                                colname      => 'B',
                                distcnt      => v_distcnt,
                                density      => v_density,
                                nullcnt      => v_nullcnt,
                                srec         => srec,
                                avgclen      => v_avgclen
                               );


   charvals := DBMS_STATS.chararray ('A_MIN', 'Z_MAX');

   DBMS_STATS.prepare_column_values (srec, charvals);

   DBMS_STATS.set_column_stats (ownname      => user,
                                tabname      => 'TEST',
                                colname      => 'B',
                                distcnt      => v_distcnt,
                                density      => v_density,
                                nullcnt      => v_nullcnt,
                                srec         => srec,
                                avgclen      => v_avgclen
                               );

   -- 日期类型

   DBMS_STATS.get_column_stats (ownname      => user,
                                tabname      => 'TEST',
                                colname      => 'C',
                                distcnt      => v_distcnt,
                                density      => v_density,
                                nullcnt      => v_nullcnt,
                                srec         => srec,
                                avgclen      => v_avgclen
                               );


   datevals := DBMS_STATS.datearray (sysdate-365, sysdate);

   DBMS_STATS.prepare_column_values (srec, datevals);

   DBMS_STATS.set_column_stats (ownname      => user,
                                tabname      => 'TEST',
                                colname      => 'C',
                                distcnt      => v_distcnt,
                                density      => v_density,
                                nullcnt      => v_nullcnt,
                                srec         => srec,
                                avgclen      => v_avgclen
                               );

COMMIT;
END;
/

--------------------------------------------------------------------
               为了显示方便，创建一个函数
--------------------------------------------------------------------
create or replace function display_raw(rawval raw, type varchar2)
return varchar2 is
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
if (type = 'NUMBER') then
    dbms_stats.convert_raw_value(rawval, cn);
    return to_char(cn);
elsif (type = 'VARCHAR2') then
    dbms_stats.convert_raw_value(rawval, cv);
    return to_char(cv);
elsif (type = 'DATE') then
    dbms_stats.convert_raw_value(rawval, cd);
    return to_char(cd);
elsif (type = 'NVARCHAR2') then
    dbms_stats.convert_raw_value(rawval, cnv);
    return to_char(cnv);
elsif (type = 'ROWID') then
    dbms_stats.convert_raw_value(rawval, cr);
    return to_char(cnv);
elsif (type = 'CHAR') then
    dbms_stats.convert_raw_value(rawval, cc);
    return to_char(cc);
else
    return 'UNKNOWN DATATYPE';
end if;
end;
/

--------------------------------------------------------------------
               设置前字段的最大值最小值
--------------------------------------------------------------------
COL LOW_VAL FOR A30
COL HIGH_VAL FOR A30
COL DATA_TYPE FOR A30
select
   a.column_name,
   display_raw(a.low_value,b.data_type) as low_val,
   display_raw(a.high_value,b.data_type) as high_val,
   b.data_type
from
   user_tab_col_statistics a, user_tab_cols b
where
   a.table_name='TEST' and
   a.table_name=b.table_name and
   a.column_name=b.column_name
/

COLUMN_NAME                    LOW_VAL                        HIGH_VAL                       DATA_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------
A                              2                              1118                           NUMBER
B                              ACCESS$                        XS$VERIFIERS                   VARCHAR2
C                              2009-08-24 14:59:03            2009-08-24 14:59:33            DATE

--------------------------------------------------------------------
设置后字段的最大值最小值
--------------------------------------------------------------------

COLUMN_NAME                    LOW_VAL                        HIGH_VAL                       DATA_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------
A                              1                              100000                         NUMBER
B                              A_MIN                          Z_MAX                          VARCHAR2
C                              2010-09-08 16:48:29            2011-09-08 16:48:29            DATE

--------------------------------------------------------------------
设置字段A的等高直方图
--------------------------------------------------------------------
declare

v_distcnt  number;
v_density  number;
v_nullcnt  number;
srec     dbms_stats.statrec;
v_avgclen  number;
n_array     dbms_stats.numarray;

begin

   DBMS_STATS.get_column_stats (ownname      => user,
                                tabname      => 'TEST',
                                colname      => 'A',
                                distcnt      => v_distcnt,
                                density      => v_density,
                                nullcnt      => v_nullcnt,
                                srec         => srec,
                                avgclen      => v_avgclen
                               );

n_array := dbms_stats.numarray(20, 60, 2000, 3000, 3000, 3000, 3000, 3000, 3000, 4000, 10000);
srec.bkvals := null;
srec.epc := 11;

dbms_stats.prepare_column_values(srec, n_array);

dbms_stats.set_column_stats(
  ownname  => user,
  tabname  => 'test',
  colname  => 'a',
  distcnt  => v_distcnt,
  density  => v_density,
  nullcnt  => v_nullcnt,
  srec  => srec,
  avgclen  => v_avgclen
);

end;
/

select endpoint_value,
       endpoint_number,
       lag(endpoint_number, 1) over(order by endpoint_number) prev_number
from user_tab_histograms
where table_name = 'TEST'
   and column_name = 'A'
order by endpoint_value;

ENDPOINT_VALUE ENDPOINT_NUMBER PREV_NUMBER
-------------- --------------- -----------
            20               0
            60               1           0
          2000               2           1
          3000               8           2
          4000               9           8
         10000              10           9

--------------------------------------------------------------------
设置等频A的等频直方图
--------------------------------------------------------------------
declare

v_distcnt  number;
v_density  number;
v_nullcnt  number;
srec     dbms_stats.statrec;
v_avgclen  number;
n_array     dbms_stats.numarray;

begin

srec.bkvals := dbms_stats.numarray(20, 60, 80,100,120, 40, 60, 80, 100, 100,240);---------------每个值所对应的频率
n_array := dbms_stats.numarray(2,40, 200,300, 400,500,600,700,800,900,1000); ---------按照大小排序
srec.epc := 11;

dbms_stats.prepare_column_values(srec, n_array);

dbms_stats.set_column_stats(
  ownname  => user,
  tabname  => 'test',
  colname  => 'a',
  distcnt  => 11,
  density  => 0.000500501,
  nullcnt  => v_nullcnt,
  srec  => srec,
  avgclen  => v_avgclen
);

end;
/
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
             20              2
             80             40
            160            200
            260            300
            380            400
            420            500
            480            600
            560            700
            660            800
            760            900
           1000           1000

select * from test where A=1000;
--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   240 | 6480 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |   240 | 6480 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------