Re: Height Balanced Histogram on a unique varchar2 column

in your example with random values I would not expect benefits - but maybe if there are few outliers resulting in a large range between low_value and high_value a frequency histogram could be useful for range queries. Something in the line of:
https://richardfoote.wordpress.com/2011/09/01/method_opt-size-auto-quiz-solution-the-trickster/.

Regards

Martin

Am 31.10.2014 12:06, schrieb Mohamed Houri:
>
> Dears,
>
>
> What benefit the CBO can gain from a unique varchar2 column having
> height balanced histogram (11.2.0.3.0) ?
>
>
> drop table t1 purge;
>
> create table t1
>
> (col1 number
>
> ,col2 varchar2(50)
>
> ,flag varchar2(2));
>
> insert into t1
>
> select
>
> rownum
>
> ,dbms_random.string('s',20)
>
> ,case when rownum = 1
>
> then 'Y1'
>
> when rownum = 2
>
> then 'Y2'
>
> when mod(rownum,2) = 0
>
> then 'N1'
>
> else 'N2'
>
> end
>
> from dual
>
> connect by rownum <= 1e5;
>
> commit;
>
> create unique index ind_t1 on t1(col2);
>
> BEGIN
>
> dbms_stats.gather_table_stats
>
> (user
>
> ,'T1'
>
> ,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY'
>
> ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
>
> ,cascade => true
>
> ,no_invalidate => FALSE);
>
> END;
>
> /
>
> select column_name
>
> ,histogram
>
> from
>
> user_tab_col_statistics
>
> where table_name = 'T1';
>
> COLUMN_NAME HISTOGRAM
>
> ------------------------------ ---------------
>
> COL1 NONE
>
> COL2HEIGHT BALANCED
>
> FLAG FREQUENCY
>
>
> Thanks
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Member of Oraworld-team <http://www.oraworld-team.com/>
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect
> -<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_
>
> My Twitter <https://twitter.com/MohamedHouri> -MohamedHouri
> <https://twitter.com/MohamedHouri>
>

--
http://www.freelists.org/webpage/oracle-l