| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> popular value is not popular in 10g?
Hi List,
while solving some funny problem concerning cardinality estimation with HB histogram I observed (Release 10.2.0.1.0, Windows) this behaviour that is neither logically explainable to me nor searchable on metalink. I’ll try to demonstrate it on a very simple example: If the column is populated as follows…
create table h (h number);
insert into h select 1 from dual connect by level <= 5; insert into h select 2 from dual connect by level <= 5; insert into h select 3 from dual connect by level <= 20; insert into h select 4 from dual connect by level <= 5; insert into h select 5 from dual connect by level <= 5;commit;
--- begin dbms_stats.gather_table_stats(ownname=>user, tabname=>'h', method_opt=>'for all columns size 4', cascade => true, estimate_percentReceived on Sun Jun 04 2006 - 20:13:42 CDT
=> 100);
end; / … a HB histogram is build and the value 3 is recognised as a popular value; SFSG. If the popular value is shifted to be the first value in the sorting order, like this … drop table h; create table h (h number); insert into h select 1 from dual connect by level <= 20; insert into h select 2 from dual connect by level <= 5; insert into h select 3 from dual connect by level <= 5; insert into h select 4 from dual connect by level <= 5; insert into h select 5 from dual connect by level <= 5; commit; --- begin dbms_stats.gather_table_stats(ownname=>user, tabname=>'h', method_opt=>'for all columns size 4', cascade => true, estimate_percent
=> 100);
end; / … HB histogram is build but suddenly the value 1 cease to be popular. Consider: SQL> EXPLAIN PLAN SET STATEMENT_ID = 'N1' into plan_table FOR 2 select * from h where h = 1; Explained. SQL> -- SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'N1','ALL')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3725287857 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 39 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| H | 13 | 39 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------- -- cut off to save space -- SQL> -- SQL> EXPLAIN PLAN SET STATEMENT_ID = 'N2' into plan_table FOR 2 select * from h where h = 2; Explained. SQL> -- SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'N2','ALL')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3725287857 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 39 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| H | 13 | 39 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------- -- cut off to save space -- … both predicates on 1 and 2 have the same selectivity, i.e. 1 is not a popular value any more. It seems to be a shift between version 9 and 10. Consider the view USER_TAB_HISTOGRAMS in release 9 and 10 for the sample data with the “leading” popular value: -- Release 10.2.0.1.0 SQL> select a.endpoint_number, a.endpoint_value,b.num_buckets, b.histogram, 2 case when histogram = 'HEIGHT BALANCED' and 3 endpoint_number -lag(endpoint_number,1,0) 4 over (order by endpoint_number) > 1 then 'popular value: '||a.endpoint_value end popular_value 5 from user_tab_histograms a, 6 (select num_buckets, num_distinct, histogram from user_tab_columns a 7 where table_name = 'H' and column_name like 'H') b 8 where table_name = 'H' and column_name = 'H' 9 order by table_name, column_name, ENDPOINT_NUMBER; ENDPOINT_NUMBER ENDPOINT_VALUE NUM_BUCKETS HISTOGRAM POPULAR_VALUE --------------- -------------- ----------- --------------- ------------------------------------------------------- 1 1 4 HEIGHT BALANCED 2 2 4 HEIGHT BALANCED 3 4 4 HEIGHT BALANCED 4 5 4 HEIGHT BALANCED SQL> -- Release 9.2.0.5.0 SQL> select a.endpoint_number, a.endpoint_value,b.num_buckets, b.histogram, 2 case when histogram = 'HEIGHT BALANCED' and 3 endpoint_number -lag(endpoint_number,1,0) 4 over (order by endpoint_number) > 1 then 'popular value: '||a.endpoint_value end popular_value 5 from user_tab_histograms a, 6 (select num_buckets, num_distinct, 'HEIGHT BALANCED' /* required in 9i */ histogram from user_tab_columns a 7 where table_name = 'H' and column_name like 'H') b 8 where table_name = 'H' and column_name = 'H' 9 order by table_name, column_name, ENDPOINT_NUMBER; ENDPOINT_NUMBER ENDPOINT_VALUE NUM_BUCKETS HISTOGRAM POPULAR_VALUE --------------- -------------- ----------- --------------- ------------------------------------------------------- 2 1 2 HEIGHT BALANCED popular value: 1 3 3 2 HEIGHT BALANCED 4 5 2 HEIGHT BALANCED SQL> So what was popular in 9i is a plebs in 10g? Any comments? Regards, Jaromir D.B. Nemec http://www.db-nemec.com -- http://www.freelists.org/webpage/oracle-l
 |
 |