“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>
SQL> create table fruit(v1 varchar2(30));
SQL>
SQL> insert into fruit values('apples');
SQL> insert into fruit values('oranges');
SQL> commit;
SQL>
SQL>
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>
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>
SQL>
SQL>
SQL> select
2 max(endpoint_value) - min(endpoint_value) diff
3 from
4 user_tab_histograms
5 where
6 table_name = 'FRUIT'
7 ;
DIFF
------------------------------------------------
72,731,533,037,581,000,000,000,000,000,000,000
SQL>
SQL> spool off
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 BST Nov 20,2014 |
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 BST Nov 21,2014 |