“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 GMT 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 GMT Nov 21,2014 |
[…] in and created a histogram of 4 buckets on the column (there’s a suitable little demo at this URL) and got the following endpoint […]
Pingback by Character selectivity | Oracle Scratchpad — April 14, 2017 @ 12:40 pm BST Apr 14,2017 |