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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-703015/