This discussion is archived
2 Replies Latest reply: Jan 22, 2013 11:45 AM by P.Forstmann RSS

Different histogram requirement on different column of a table

rahulras Explorer
Currently Being Moderated
Hi All,

I am on 11.2.0.3.
In my trying to understand/explore the depth in which we can collect statistics on a table. At the moment, I am only running Oracle's default stats collection job.
As an example to my question, please consider this table
create table transactions
(
trans_id           number,  -- unique values, primary key
trans_description  varchar2(500),
trans_type         varchar(20),  -- highly skewed, highly used, need perfect histogram
trans_comments     varchar2(500),
country            varchar2(100), -- highly skewed, moderately used, need 'a' histogram
insert_timestamp   timestamp,
update_timestamp   timestamp,
expire_timestamp   timestamp
)
It is safe to say that, this table will have 100s of millions of records.
Lets say, there is a bitmap index on trans_type and btree index on country (does the type of index matter?!).
Ideally, I want a histogram in trans_type which is created using 100% estimate size. Country column should have a histogram which is created using decent estimate percentage. Other columns, I don't care if there is any histogram, rather I don't want histograms on them (coz existance of histogram indicates skewed data).
How can I achieve that?
In 11g, using some complex METHOD_OPT in DBMS_STATS, is it possible to create (and not create) histograms on selected columns only? that too with different estimate percent?
Can I remove certain histograms later?
Is it possible to update statistics only for certain column(s)?
I am reading the manual pages for DBMS_STATS, but struggling.

Thanks in advance
  • 1. Re: Different histogram requirement on different column of a table
    damorgan Oracle ACE Director
    Currently Being Moderated
    Warning lights are blinking as I read what you wrote. I start looking around for a crash helmet when I read:
    Lets say, there is a bitmap index on trans_type and btree index on country (does the type of index matter?!).
    It matter very very much and the fact that you have asked this question clearly indicates you shouldn't write a single line of DDL until you fully understand why. And you've not provided a single byte of information indicating the type of use this application and table will perform, whether single-instance or RAC, whether it will use replication, etc.

    Then we can get to histograms and I get seriously concerned that you haven't read all of the expert advice on when histograms should be used, the warnings and cautions around their usage, or whether you are even aware that there are different types of histograms.

    So my only recommendations to you at this points are:
    1. Read the docs at http://tahiti.oracle.com
    2. Read Jonathan Lewis' blog comment on histograms
    3. Read Richard Foote and Jonathan Lewis; comments on bitmap indexes

    Then, and only then, should you concern yourself with anything other than the default stats collection job.

    PS: Until you have proven, with testing, that you can do better than the default ... and by better I mean produce a better execution plan ... leave it alone.
  • 2. Re: Different histogram requirement on different column of a table
    P.Forstmann Guru
    Currently Being Moderated
    You can also find interesting examples on Daniel Morgan's site http://www.morganslibrary.org/reference/histogram.html.
    To generate histogram for a specific column, read section starting with:
    dbms_stats.gather_table_stats(<schema_name>, <table_name>,
    METHOD_OPT => FOR COLUMN SIZE <integer> <column_name>
    To delete histogram for a specific column you should use DBMB_STATS.DELETE_COLUMN_STATS
    with:
    col_stat_type => 'HISTOGRAM'
    See http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#ARPLS68510.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points