Re: Histograms

This isn't strictly true.

Imagine tableA and tableB have 1,000,000 rows each, and you have a query with a WHERE clause like:

    where A.col1 = 745
    and B.col2 = 456
    and A.col3 = B.col3

Assume no indexes at all and the init.ora is set so that the join condition is likely to be a hash join.

If A.col1 and B.col2 have histograms on them, it may allow Oracle to make a good choice as to which table should be the first table in the hash join.

Whenever information about the selectivity of a column can affect the optimiser path, then a histogram could make a difference. However histograms are fairly expensive, so I would only worry about them for columns where the values had a highly skewed distribution, the column was frequently involved in non-trivial queries, and significantly different access paths would make a big difference - i.e. in very cases, and where you really understand the data.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jan Syssauw wrote in message <7croec$jql$1_at_xenon.inbe.net>...

>histograms on a non-indexed column: no use at all


>        histograms are used to help the optimizer determine if it is of any


>        interest to use the index of not (% of rows returned)