Re: re "CBO - A Configuration Roadmap" -- Histograms on Non-Indexed Columns
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: hkchital@xxxxxxxxxxxxxx
- Date: Fri, 29 Dec 2006 12:00:29 -0800
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 **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
