Re: re "CBO - A Configuration Roadmap" -- Histograms on Non-Indexed Columns

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 28 Dec 2006 09:44:30 -0700
Message-Id: <20061228164339.3F76657D130@turing.freelists.org>

At 09:12 AM 12/28/2006, Hemant K Chitale wrote:

>Can you explain the second sentence about Histograms on non-indexed
>columns as well ?
>How would they be useful ? I would understand Histograms on
>non-indexed columns
>as providing _me_ information about the data in those columns and
>which could allow
>me to make a judgement call as to whether I should index the
>columns. How does the
>optimizer use Histograms on non-indexed columns ?

Column statistics give the CBO information about the selectivity of predicates on that column. Predicate selectivity in turn gives rise to cardinality estimates. Differences in selectivity, e.g. by having a histogram, lead to differences in cardinality estimates which in turn can, and does, lead to different execution plans and peformance. For a very simple example see my paper on "Histograms - Myths and facts".

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

---

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

---

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 28 2006 - 10:44:30 CST