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

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 owner, table , 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 **

Other related posts: