My thoughts - others may differ:
- 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)
- 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.
- 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 <mjgnmenon_at_yahoo.com> 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: connor_mcdonald_at_yahoo.com
"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: hamcdc_at_yahoo.co.uk
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: ListGuru_at_fatcity.com (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).
Received on Sat Feb 08 2003 - 04:04:13 CST
