| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ** find whether table or index being accessed
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 rjesse_at_qtiworld.com 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: Rich.Jesse_at_qtiworld.com
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: ListGuru_at_fatcity.com (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
 |
 |