My thoughts - others may differ: a) Only have histograms where absolutely necessary. If you've got large histograms on every table columns thats a lot of stuff sucking up dictionary cache space which will never get used. Similarly, it would probably be more effort to parse (although I have not quantified that)
b) The number of distinct/duplicate values should really be relevant - its whether you will regularly need to probe a table using column values that are skewed in such a way as to have the optimizer make poor assumptions about their distribution. c) You may want consider playing with the automatic histogram collection in 9i, where a hidden table is regularly populated with the columns and predicates you've used and thus can make some decisions on whether a histogram may benefit. hth connor --- Murali Menon <[EMAIL PROTECTED]> wrote: > > So far only had to use RBO, now we are moving > towards using CBO. However none of the documents > really talk in detail about Histograms, the goods, > bads where and where not to use. > > I have read that HISTOGRAMS are good for columns > that are non unique and that have many duplicate > values. While DSS systems have more occurances of > these kind of columns, OLTP databases could also > have certain columns like status codes or state > codes that have duplicate values. > > Is it OK to have histograms generated for all > columns irrespective of the type of system the > database supports? and pros and cons? > > Could some one point me to a good white paper or > material that would discuss this? > > TIA > > Menon > > > > --------------------------------- > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
