Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ** find whether table or index being accessed

RE: ** find whether table or index being accessed

From: Jesse, Rich <>
Date: Wed, 19 Nov 2003 08:14:58 -0800
Message-ID: <>

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 Jesse                           System/Database Administrator                  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, 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:

Author: Jesse, Rich

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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). Received on Wed Nov 19 2003 - 10:14:58 CST

Original text of this message