Oracle Scratchpad

November 19, 2014


Filed under: Histograms,humour,Oracle,Statistics — Jonathan Lewis @ 12:47 pm GMT Nov 19,2014

“You can’t compare apples with oranges.”

Oh, yes you can! The answer is 72,731,533,037,581,000,000,000,000,000,000,000.

SQL> create table fruit(v1 varchar2(30));
SQL> insert into fruit values('apples');
SQL> insert into fruit values('oranges');
SQL> commit;
SQL> begin
  2  	     dbms_stats.gather_table_stats(
  3  		     ownname	      => user,
  4  		     tabname	      =>'FRUIT',
  5  		     method_opt       => 'for all columns size 2'
  6  	     );
  7  end;
  8  /
SQL> select
  2  	     endpoint_number,
  3  	     endpoint_value,
  4  	     to_char(endpoint_value,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') hex_value
  5  from
  6  	     user_tab_histograms
  7  where
  8  	     table_name = 'FRUIT'
  9  order by
 10  	     endpoint_number
 11  ;

ENDPOINT_NUMBER                                   ENDPOINT_VALUE HEX_VALUE
--------------- ------------------------------------------------ -------------------------------
              1  505,933,332,254,715,000,000,000,000,000,000,000  6170706c65731ad171a7dca6e00000
              2  578,664,865,292,296,000,000,000,000,000,000,000  6f72616e67658acc6c9dcaf5000000
SQL> select
  2  	     max(endpoint_value) - min(endpoint_value) diff
  3  from
  4  	     user_tab_histograms
  5  where
  6  	     table_name = 'FRUIT'
  7  ;

SQL> spool off


  1. Hi Jonathan

    As everyone know that two “elements or quantity” with same unit are comparable.
    But you actually(also) have demonstrated “difference” of two literals(varchar/string elements).
    I don`t know what was your purpose of this post exactly. But I think it is possible finding difference of such
    elements without create histogram or table. As you explained how endpoint_value(actually histgrm$.epvalue) stored/calculated in your
    “Cost Based Oracle Fundamentals” book.(I think we can create simple procedure/function for that)

    – Finding hex representation of these elements(strings) with 32 byte
    – Extracting 15 bytes of that representation and padding zeros
    – Converting that hex number to decimal number
    – Finding difference of last numbers.

    Comment by Chinar Aliyev — November 20, 2014 @ 5:21 am GMT Nov 20,2014 | Reply

    • Chinar,

      You might note that one of the tags on this post was “humour” – it was a moment of whimsy after my DOAG presentation on simple selectivity.
      On a slightly more serious note: in order to justify using the calculation from the book in the blog I would have had to demonstrate that it was correctly derived from the histogram anyway, so creating the histogram by creating the correct data was a much clearer simpler exposition.

      Comment by Jonathan Lewis — November 21, 2014 @ 2:51 pm GMT Nov 21,2014 | 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: Logo

You are commenting using your 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

Powered by