ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过dbms_stats包修改统计信息里的最大值最小值,等频等高直方图

通过dbms_stats包修改统计信息里的最大值最小值,等频等高直方图

原创 Linux操作系统 作者:wei-xh 时间:2011-09-08 18:29:01 0 删除 编辑

--------------------------------------------------------------------
                 创建测试表
--------------------------------------------------------------------
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

   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
                               );

 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 |
--------------------------------------------------------------------------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-706944/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315251