Re: Oracle Index Question

On Jan 15, 11:49 am, Jimbo <jamesfer..._at_hotmail.com> wrote:
> I work primarily in SQL Server and have run across a situatiion in
> Oracle that's troubling me....
>
> I have a query that contains a where clause with 2 restrictions.  Both
> these fields are in a composite index of 3 fields.  If I add the third
> field to the restriction the optimizer uses the index, however if i
> keep it with just the 2 fields it does a table scan and takes forever
> unless I put in an index hint to use the composite index.  Is this
> right?..is this a dba issue?

The optimizer only can work with the information it is given. It may or may not be a DBA issue, that depends on the division of responsibilities at your site. I would expect the maintenance of indices to be under the DBA purview.

What you as a developer need to do is become familiar with the tools available to you. You _must_ become adept with reading and using explain plans. You should also become familiar with tracing for the more in-depth problems. You need to be able to work with your DBA to resolve problems.

You should look at the doc set, especially the concepts manual and the performance guide, all the basics are there. Tuning is a big subject, the best book on the optimizer is by Jonathan Lewis. Other books you will find useful are by Tom Kyte and Cary Milsap. They all show how to determine what is going on, rather than guess or give overly-broad rules of thumb. There have been many expositions here and elsewhere on the web on how to figure this stuff out, but it really helps to have a foundation in the basics first, then work examples.

In general it is better to tell the optimizer enough for it to figure the correct course than to use hints. To get past this problem quickly you might want to look into plan stability (which uses hints, of course). The optimizer uses costing to figure out which way to access data, with just the two fields it figures it is cheaper to do the table scan.

If you set autotrace and post the plan here some might help you. It's best if you post a complete test case, including code and data.

Always be sure and post the exact version and exact hardware/OS version you are using. It can make a difference. I'm assuming a lot, you could be on Oracle7 with the rule based optimizer...

jg

--
@home.com is bogus.  "Flying the airplane is more important than
radioing your plight to a person on the ground who is incapable of
understanding or doing anything about it."