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