Oracle Scratchpad

November 29, 2006

low_value / high_value

Filed under: CBO,Statistics,Troubleshooting — Jonathan Lewis @ 12:01 pm BST Nov 29,2006

If you take a look at view user_tab_columns (or equivalent) you will find that its definition includes columns low_value and high_value, which give the currently known low and high value for the data. For example:

 
select 
	column_name, low_value, high_value 
from 
	user_tab_columns 
where 
	table_name  = 'T1' 
and	column_name = 'SKEW' 
;         

COLUMN_NAME          LOW_VALUE       HIGH_VALUE 
-------------------- --------------- --------------- 
SKEW                 C102            C151       

This is fine, of course, if you are good at translating internal forms of data into human-readable forms.

For those of us less gifted, the dbms_stats package gives us some help with a set of procedures with names like convert_raw_value(). There are three overloaded functions to handle varchar2, date, and number types (with binary_float and binary_double appearing as new options in 10g), and two special cases: convert_raw_value_nvarchar() and convert_raw_value_rowid(). The procedures all take two parameters – the first in an input which is the raw value to be converted, the second is an output which has to be a variable of the correct type, so, for example:

 
SQL> declare 
  2     m_n     number; 
  3  begin 
  4     dbms_stats.convert_raw_value('C102',m_n); 
  5     dbms_output.put_line('Result: ' || m_n); 
  6  end; 
  7  . 
SQL> / 
Result: 1        

For convenience, you can wrap calls to the procedures inside functions, which then makes them available for use in SQL.

The following example is just to get you started (the column called skew is one I know to be a number type) – you would probably want to create a package to hold several different functions, and declare the functions as deterministic.

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

select 
	column_name, 
	raw_to_num(low_value)		low_val, 
	raw_to_num(high_value)		high_val 
from 
	user_tab_columns 
where 
	table_name  = 'T1' 
and	column_name = 'SKEW' 
;     

COLUMN_NAME             LOW_VAL   HIGH_VAL 
-------------------- ---------- ---------- 
SKEW                          1         80     

To finish off, you would probably want to query the user_tab_columns (or related) views with a decode() on the data_type to decide which of the functions to use, and then perform an explicit to_char() on the result of the decode(). But that task is left as an exercise for the reader.

Addendum: If you read the comments you will see that some of them have taken up the exercise and supplied some answers.

10 Comments »

  1. create or replace function raw_to_num(i_raw raw)
    return number
    as
    	m_n number;
    begin
    	dbms_stats.convert_raw_value(i_raw,m_n);
    	return m_n;
    end;
    /  
    
    create or replace function raw_to_date(i_raw raw)
    return date
    as
    	m_n date;
    begin
    	dbms_stats.convert_raw_value(i_raw,m_n);
    	return m_n;
    end;
    /  
    
    create or replace function raw_to_varchar2(i_raw raw)
    return varchar2
    as
    	m_n varchar2(20);
    begin
    	dbms_stats.convert_raw_value(i_raw,m_n);
    	return m_n;
    end;
    / 
    
    select 
            column_name,
            decode(data_type,
                    'VARCHAR2',to_char(raw_to_varchar2(low_value)),
                    'DATE',to_char(raw_to_date(low_value)),
                    'NUMBER',to_char(raw_to_num(low_value))
            ) low_value,
            decode(data_type,
                    'VARCHAR2',to_char(raw_to_varchar2(high_value)),
                    'DATE',to_char(raw_to_date(high_value)),
                    'NUMBER',to_char(raw_to_num(high_value))
            ) high_value
    from user_tab_columns 
    where table_name='';
    

    Exercise done.- reader

    Comment by anonymous — November 29, 2006 @ 8:02 pm BST Nov 29,2006 | Reply

  2. Seems funny that Oracle should put these procedures in the DBMS_STATS package when the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.

    Cheers

    Tim…

    Comment by Tim Hall — November 30, 2006 @ 8:01 am BST Nov 30,2006 | Reply

  3. We have found out dependence of LOW_VALUE, HIGH_VALUE from parameters NLS_COMP, NLS_SORT. However DBMS_STATS documentation is not present the description of such behaviour…

    Comment by Pavel Kozlov — June 7, 2007 @ 8:10 am BST Jun 7,2007 | Reply

  4. I am attempting to change the statistics value of high_value with the help of dbms_stats.set_column_stats. For some reason I struggle to find a function to convert date to raw. Am I looking in the wrong place or am I missing something?

    Comment by Jan Rod — August 24, 2009 @ 7:13 pm BST Aug 24,2009 | Reply

    • Jan,

      you don’t have to convert DATE to RAW when setting low/high value. Just let the dbms_stats.prepare_column_stats do the dirty work for you. I hope there are no bug in my EXAMPLE :)

      create or replace function raw_to_date(i_raw raw)
      return date
      as
      m_n date;
      begin
      dbms_stats.convert_raw_value(i_raw,m_n);
      return m_n;
      end;
      / 
      
      ALTER SESSION SET NLS_DATE_FORMAT='YYYY.MM.DD';
      
      CREATE TABLE t_d1 (d_date DATE);
      
      INSERT INTO t_d1 VALUES(TO_DATE('2009.08.01', 'YYYY.MM.DD'));
      INSERT INTO t_d1 VALUES(TO_DATE('2009.08.31', 'YYYY.MM.DD'));
      COMMIT;
      
      --gather_stats
      BEGIN
         DBMS_STATS.gather_table_stats (user,
                                        'T_D1',
                                        estimate_percent => NULL,
                                        method_opt => 'FOR ALL COLUMNS SIZE 1',
                                        CASCADE => TRUE,
                                        FORCE => TRUE
                                       );
      END;
      /
      
      --show low/high val
      SELECT low_value, raw_to_date(low_value), high_value, raw_to_date(high_value)
      FROM user_tab_col_statistics
      WHERE table_name = 'T_D1' AND column_name = 'D_DATE'
      /
      
      /*  !!!RESULT!!!
      
      LOW_VALUE            RAW_TO_DAT HIGH_VALUE           RAW_TO_DAT
      -------------------- ---------- -------------------- ----------
      786D0801010101       2009.08.01 786D081F010101       2009.08.31
      */
      
      
      --increase high value by ONE MONTH
      
      DECLARE
         n_distcnt          NUMBER;
         n_density          NUMBER;
         n_nullcnt          NUMBER;
         rec_srec           DBMS_STATS.statrec;
         datevals           DBMS_STATS.DATEARRAY;
         n_avgclen          NUMBER;
         d_low              DATE;
         d_high             DATE;
      BEGIN
         DBMS_STATS.get_column_stats (USER,
                                      'T_D1',
                                      'D_DATE',
                                      distcnt      => n_distcnt,
                                      density      => n_density,
                                      nullcnt      => n_nullcnt,
                                      srec         => rec_srec,
                                      avgclen      => n_avgclen
                                     );
         
         --handles just the case when HISTOGRAMS are not set -> rec_srec.epc = 2
         IF rec_srec.epc = 2 THEN
           SELECT raw_to_date(low_value), raw_to_date(high_value) INTO d_low, d_high
              FROM user_tab_col_statistics
            WHERE table_name = 'T_D1' AND column_name = 'D_DATE';
           
           d_high := ADD_MONTHS(d_high, 1);
           datevals := DBMS_STATS.DATEARRAY(d_low, d_high);
           rec_srec.minval:=NULL;
           rec_srec.maxval:=NULL;
           rec_srec.bkvals:=NULL;
           rec_srec.novals:=NULL;
           
           --this procedure will set epc.minval, epc.maxval etc in INTERNAL FORMAT
           DBMS_STATS.PREPARE_COLUMN_VALUES(rec_srec, datevals);
           
           --and then just set statistics
           DBMS_STATS.set_column_stats (USER,
                                        'T_D1',
                                        'D_DATE',
                                        distcnt      => n_distcnt,
                                        density      => n_density,
                                        nullcnt      => n_nullcnt,
                                        srec         => rec_srec,
                                        avgclen      => n_avgclen
                                        );
         END IF;    
      END;
      /
      
      --show low/high val , HIGH_VAL IS increased
      SELECT low_value, raw_to_date(low_value), high_value, raw_to_date(high_value)
      FROM user_tab_col_statistics
      WHERE table_name = 'T_D1' AND column_name = 'D_DATE'
      /
      
      
      /*  !!!RESULT!!!
      LOW_VALUE            RAW_TO_DAT HIGH_VALUE           RAW_TO_DAT
      -------------------- ---------- -------------------- ----------
      786D0801010101       2009.08.01 786D091E010101       2009.09.30
      */
      

      Comment by Pavol Babel — August 24, 2009 @ 8:42 pm BST Aug 24,2009 | Reply

      • Can you please tel me what &gt is used for and how ? what do i enter when i get prompt ? thanks

        Comment by Jim — January 20, 2012 @ 8:47 pm BST Jan 20,2012 | Reply

        • This is a side effect of wordpress and it’s handling of the “greater than” sign. In some cases it comes out as “>”. I’ve fixed it in the example above.

          Comment by Jonathan Lewis — January 21, 2012 @ 5:04 pm BST Jan 21,2012

        • thanx jonathan for repairing my example. As far I can remember it had been written before wordpress started to support sourcecode tag (so formating was poor). You have obviously fixed it layter, but &gt remained :) Now it seems to be 100% correct.

          Comment by Pavol Babel — January 21, 2012 @ 10:57 pm BST Jan 21,2012

    • BTW, you could set the RAW value directly into srec.maxval . But you have to write your own RAW TO DATE function.

      RAW representation of DATE in user_tab_columns follows the OCI 7BYTE representation of DATE. You can read the OCI Docmuentation to understand it.

      select d_date,  dump(d_date) dump_date FROM t_d1
      /
      
      D_DATE     DUMP_DATE
      ---------- ------------------------------------
      2009.08.01 Typ=12 Len=7: 120,109,8,1,1,1,1
      2009.08.31 Typ=12 Len=7: 120,109,8,31,1,1,1
      

      If you convert each number to HEXA and concat them, you’ll get RAW from low/high_val from user_tab_columns/user_tab_col_statistics

      Comment by Pavol Babel — August 24, 2009 @ 8:56 pm BST Aug 24,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers