Histograms
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Histograms

  1. #1
    Join Date
    Nov 2000
    Posts
    20

    Angry

    Hai,
    Can any one say what is the need of creating a histogram . I had noticed that when i analyze the table itself it is putting the entry in dba_histogram table. Is this sufficient ..? what is the need of specifying bucket size..? Does this boost up any performance ..?


    Thanks ...Thomas
    Thomas

  2. #2
    Join Date
    Sep 2000
    Posts
    128
    Creating a histogram gives the optimiser more information on indexed columns where data is skewed.

    For example (From my system):
    I have a Country column which contains 12 different values. The table has 3.5 million rows.
    A bitmap index is created on the table, but one day the optimiser changed to a full table scan over an index scan.

    Since some countries are quite new in this table, the have only a few records e.g. maybe a couple of thousand. I created a histogram on this column, and the optimiser went back to using the index for most countries.

    So it is not guarenteed to enhance performance, but may lead to better performance since the optimiser can make better choices on it's execution plan.

    Terry

  3. #3
    Join Date
    Nov 2000
    Posts
    20

    Angry

    Terry,

    But when i analyze the table itself it is creating a histogram. Why should i Specifically create a new one...? And what is a bucket size..?


    ...thomas
    Thomas

  4. #4
    Join Date
    Sep 2000
    Posts
    128
    Yes, there are entries in there, but without the for clause it creates a histogram with a bucket size of 1... The optimiser doesn't consider this as a histogram.

    If you create a histogram, you will see more entries for the column.

    e.g. (on my db)

    analyze table t_entity compute statistics;
    select * from dba_histograms where table_name = 'T_ENTITY' and column_name = 'COUNTRY_ID'
    --> 2 rows selected

    analyze table t_entity compute statistics for columns COUNTRY_ID size 75;
    (75 is default)
    select * from dba_histograms where table_name = 'T_ENTITY' and column_name = 'COUNTRY_ID'
    --> 14 rows selected

    Terry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width