ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何通过dbms_stats包修改列的最大最小值

如何通过dbms_stats包修改列的最大最小值

原创 Linux操作系统 作者:wei-xh 时间:2011-07-25 13:13:08 0 删除 编辑

Goal

It is possible that we need to set statistics manually, for example,

- when it takes a long time to gather stats for a big table
- when we want to force a specific execution plan

But, it is not simple to set LOW_VALUE and HIGH_VALUE. This example explains how to set LOW_VALUE and HIGH_VALUE of a column manually for data types NUMBER, CHAR, DATE.

Solution

conn /

drop table TEST;
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;

EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST');

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

   -- Number Type
   
   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
                               );
               
   -- Character Type
                               
   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
                               );
                            
   -- Date Type
                               
   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;
/


-- You can check the modified values like below. 
-- First, you need to create some functions to return actual values from raw values.

create or replace function raw_to_num(rawval raw)
return number
as
v number;
begin
dbms_stats.convert_raw_value(rawval,v);
return v;
end;
/

create or replace function raw_to_date(rawval raw)
return date
as
v date;
begin
dbms_stats.convert_raw_value(rawval,v);
return v;
end;
/

create or replace function raw_to_varchar2(rawval raw)
return varchar2
as
v varchar2(20);
begin
dbms_stats.convert_raw_value(rawval,v);
return v;
end;
/


-- Now, you can see the actual values of low_value and high_value.

set pages 200 lines 200
col low_value  for a20
col high_value for a20
col low_value_number  for 99999999.99
col high_value_number for 99999999.99
col low_value_actual  for a32
col high_value_actual for a32

SELECT low_value, raw_to_num(low_value) low_value_number, high_value, raw_to_num(high_value) high_value_number
  FROM user_tab_col_statistics
 WHERE table_name = 'TEST' 
   AND column_name = 'A';

LOW_VALUE            LOW_VALUE_NUMBER HIGH_VALUE           HIGH_VALUE_NUMBER
-------------------- ---------------- -------------------- -----------------
C102                             1.00 C30B                         100000.00


SELECT low_value, raw_to_varchar2(low_value)  low_value_actual, high_value, raw_to_varchar2(high_value)  high_value_actual
  FROM user_tab_col_statistics
 WHERE table_name = 'TEST' 
   AND column_name = 'B';

LOW_VALUE            LOW_VALUE_ACTUAL                 HIGH_VALUE           HIGH_VALUE_ACTUAL
-------------------- -------------------------------- -------------------- --------------------------------
415F4D494E           A_MIN                            5A5F4D4158           Z_MAX


SELECT low_value, raw_to_date(low_value)  low_value_actual, high_value, raw_to_date(high_value)  high_value_actual
  FROM user_tab_col_statistics
 WHERE table_name = 'TEST' 
   AND column_name = 'C';

LOW_VALUE            LOW_VALUE_ACTUAL                 HIGH_VALUE           HIGH_VALUE_ACTUAL
-------------------- -------------------------------- -------------------- --------------------------------
786D0C170C132C       23-DEC-09                        786E0C170C132C       23-DEC-10

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocDsrc=KB&bmDocID=1276174.1&bmDocType=HOWTO&viewingMode=1143&bmDocTitle=An%20example%20of%20SET_COLUMN_STATS%20to%20set%20LOW_VALUE%20and%20HIGH_VALUE%20of%20a%20column%20manually&from=BOOKMARK))

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

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

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315211