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

Hemant, histograms provide input to CBO, which creates an execution plan. For what decision can histogram on an unidexed column be useful? Such histogram can be used to decide between sort/merge join or hash join. The resulting difference in execution speed can be dramatic.

Mladen Gogala
Sr. Oracle DBA
1500 Broadway, 6th floor
New York, NY 10036
(212) 329-5201
www.vmsinfo.com

The Leader in Integrated Media Intelligence Solutions

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-
> bounce_at_freelists.org] On Behalf Of Hemant K Chitale
> Sent: Thursday, December 28, 2006 11:12 AM
> To: christian.antognini_at_trivadis.com
> Cc: oracle-l_at_freelists.org
> Subject: re "CBO - A Configuration Roadmap" -- Histograms on Non-
> Indexed Columns
>
>
> Christian,
>
> I was reading your document "CBO - A Configuration Roadmap" .
>
> You write :
> "Histograms are essential for all columns referenced in WHERE
> clauses
> that contain
> skewed data. Notice that they are useful on non-indexed columns as
> well! For simplicity
> use SIZE SKEWONLY. If it takes too much time try SIZE AUTO6. If it's
> still too slow or
> the chosen number of buckets is not good (or the needed histogram
> isn't created at all),
> manually specify the list of columns."
>
> 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 ?
>
> If you do not mind [as you do post frequently at ORACLE-L], I have
> CC'd this to ORACLE-L
> trusting that your reply might also be of interest to others on the
> list.
>
>
> Hemant K Chitale
> http://web.singnet.com.sg/~hkchital
>
>
> --
> http://www.freelists.org/webpage/oracle-l

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