Re: Height Balanced Histogram on a unique varchar2 column
- From: Timur Akhmadeev <timur.akhmadeev@xxxxxxxxx>
- To: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- Date: Fri, 31 Oct 2014 14:43:34 +0300
Unique does not necessarily means uniform. There may be "holes" in values
explaining why Oracle creates a histogram here.
On Fri, Oct 31, 2014 at 2:06 PM, Mohamed Houri <mohamed.houri@xxxxxxxxx>
wrote:
> 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
>
> COL2 HEIGHT 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>
>
>
--
Regards
Timur Akhmadeev
Other related posts: