The last statement here is not always true under CBO, at least in 8i. All unique indexes (no PKs, no FKs) on our 3rd-party ERP tables are segmented, and the last segment is always company code. Even though there is only one company code value for all rows in all tables, some queries that do not specify that column will not use that index. That was probably the biggest pain going from RBO to CBO for us.
So, for your example, joining YOURTABLE to MYTABLE using only N1 and N2 does not necessarily mean that index IDX2 will be used. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -----Original Message----- Sent: Tuesday, November 18, 2003 4:20 PM To: Multiple recipients of list ORACLE-L I was too lazy to look for it on asktom.oracle.com, but here's what I read at the site a while ago (if you search on index usage or something like that you should find Mr. Kyte's answer). Tom Kyte has the following suggestions: a) In Oracle 8.0 and earlier - put an index all by itself in a tablespace, and check reads and writes on the tablespace. If reads are close to writes - index not being used (only read for updates.) If reads much larger than writes - indexes being used. b) In Oracle 9.0 and later - use alter index ... monitoring and check v$object_usage c) In Oracle 8.1 (your case): See Chapter 11 of his book "expert one-on-one Oracle" - use stored outlines. Use an ON LOGON trigger to enable automatic outline generation (and disable it after a while) - look in user_outline_hints to see if the index is being used. Finally, even though an index is used, that doesn't mean it's necessary. e.g. if you have index IDX1 on MYTABLE (N1, N2) and index IDX2 on MYTABLE (N1, N2, N3) IDX1 may be used by some queries but might not be necessary because the query could use IDX2. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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).
