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

Hemant,
You should be able to use SYS.COL_USAGE$ to work out which Columns are
being used in Join predicates using the following SQL:

select r.name owner, o.name table , c.name column,
equality_preds, equijoin_preds, nonequijoin_preds, range_preds,
like_preds, null_preds, timestamp
from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
where o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol#
and o.owner# = r.user# and (u.equijoin_preds > 0 or u.nonequijoin_preds > 0);

A MINUS against DBA_IND_COLUMNS should show up which columns *might*
need Histograms....

Titbit: As per Oracle kernel developers that I spoke to at OOW 06, the
data in COL_USAGE is never "flushed". However, they stated that only
rows with TIMESTAMP > 6 months are considered.

--
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l


Other related posts: