Hi!
If you describe this table then you see that this table stores column usage information in filter and join predicates for database objects. From describe, you see there are several filter and join conditions tracked for an object's (obj#) columns (intcol#). You can join them to col$ table for example.
During shutdown, the session executing shutdown, writes the column usage statistics to col_usage$. During normal operations, it's SMON who's doing that over regular intervals.
You can disable collecting these statistics by setting _column_tracking_level to 0.
I don't really see where CBO could use those statistics for speeding up statement execution, because during execution CBO knows all the predicates & statement structure anyway. But it is probably useful for various 10g's advisories, which can make you recommendations based on how the tables (columns) are used. Also, it might help automatic statistics gathering to determine which stats need to be updated or not (this gather stale stuff).
Which statistics may be stale is tracked by SYS.MON_MODS$. col_usage$ tracks the use of columns as predicates, not any updates. It is currently (Oracle 9) used to decide if i might be worth gathering histogram information for a column - together with the determination if the data in the column is sufficiently skewed. No point in gathering histograms on non-skewed data or on data that is never referenced in a predicate.
I can imagine that Oracle 10 will use that data to recommend indexes - as Daniel suggested.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
