Re: Re: explain plan conundrum

Histograms are only used to refine the selectivity of a predicate. This in turn determines the cardinality estimate and various costs such as index access cost and then of course join costs (NL, sort-merge, and hash) and join cardinality. This ultimately will drive the decision whether a particular index access looks more promising (i.e. has a cheaper estimated cost than an FTS) and which join order together with which join method looks most promising - has the cheapest overall cost.

It is all driven by the estimated costs, which are driven by the estimated cardinalities, which are driven by the estimated selectivities.

BTW. Histograms on non-indexed columns also affect the cardinality estimate when they are used in the where clause, which is why it is not enough to collect histograms "for all indexed columns". Conversely, most likely not all indexed (much less ALL) columns require a histogram. Histograms, and the number of their buckets, need to be chosen on a column by column basis, not with a broad brush such as "for all columns" or "for all indexed columns". In the best case it is a waste of resources to gather them, but it easily also can be detrimental to the performance.

At 10:04 AM 11/5/2003, you wrote:
>are histograms only used to determine whether to use an index or join
>type, not join order?

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.co

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

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).