| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> queries not utilizing indexes - any ideas?
Hi Mike,
a couple of questions :
what is the optimizer mode (rule or choose - or something else ?)
how many rows in each table ?
what is the db_file_multi_block_read_count parameter set to ?
what is the explain plan for the query ?
Are your statistics up to date on these tables (if cost based optimiser is being used)
Are the indexed columns 'useful' in that there is a good wide spread of values and not a lot of one or two values ?
Do you have histograms on the columns in question ?
Is the code using bind variables - this would undo the usefulness of the histograms as Oracle 817 doesn't know what value is to be used and cannot look it up to find the skew for that column value.
What is the SQL in question ?
Etc.
As you can see there are many reasons wht Oracle might choose to not use an index.
If there are not many rows in the table(s) then Oracle might decide to do a full scan on the grounds that it can read everything in very few block accesses then filter out the non required rows. If the stats are out of date it could be thinking (!) that there are only a few rows in the table rather than the 'millions' that there are currently.
This will probably be the case if DB_F_M_B_R_C is set too high as well. It could favour a FTS over indexed lookup.
Also, what is the depth and clustering factors for the indexes - if the indexes need rebuilding (unlikely, but still remotely possible, it depends) then the FTS may well be quicker than the indexed access.
Cheers,
Norm.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: mscgloss_at_yahoo.com (Ixnay) [mailto:mscgloss_at_yahoo.com]
Posted At: Wednesday, August 06, 2003 5:13 PM
Posted To: misc
Conversation: queries not utilizing indexes - any ideas?
Subject: queries not utilizing indexes - any ideas?
Hello All,
I have an issue that seems a bit odd: I have a query that is joining two tables, and the join columns are both indexed in their respective tables. Yet the query doesn't seem to utilize the indexes and runs extremely slow. Our DBA stated that "the oracle execution plan doesn't choose to use the index." Now I'm no DBA, but that just doesn't seem right. I've searched on-line and off-line documentation but nothing seems to match up with this issue.
I know this description of my issue is probably woefully lacking in relevant info, but can anyone offer any insight as to why a particular query would not utilize the indexes?
I'm running Oracle 8.1.7.
Thank you in advance,
Mike Received on Wed Aug 06 2003 - 11:41:43 CDT
 |
 |