Re: Index not used by query

Hi.
There are a number of possibilities here:

1. If your field "Type" is defined as a varchar2 datatype, then where type = 4 will be implicitly converted to where to_number(type) = 4, hence not using the index.

Use where type = '4' instaed.

2. Assuming that you don't have this implicit conversion problem,

   and assuming that you're using the cost-based optimizer  (by explicitly choosing this or by having your optimizer goal set to choose and your table analysed), then the optimizer's intelligence is telling it to use a full scan. To overcome this, try either of the following:

1. change the optimizer mode to optimize the response time of the first record rather than the throughput. i.e issue the following statment from sqlplus before running the query:

alter session set optimizer_mode= first_rows;

You may find that the index is now used.

Changing the optimizer mode back to ALL_ROWS subsequently may then use the full table scan again

b. Use a hint to tell the optimizer to use the index:

 select /*+ index(tablename_alias index_name) */ * from table tablename_alias where type = 4;

Hope this helps!
Coakleyj

In article <376ed837.15778988_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to "Frank Calfo" <fcalfo_at_psateam.com>
> (if that email address didn't require changing)
> On 10 Jun 1999 14:29:14 GMT, you wrote:
>
> >I'm doing a simple test on index usage:
> >
> >I have a table, Employee, with the columns: empid,
> > name,
> > type
> >
> >I have a simple query: select * from employee where type = 4 ;
> >
> >When I run an execution plan on this statement it indicates that
> >Oracle will do a full table scan on the employee table.
> >
> >This is as expected
> >
> >I then add a nonunique index to the table consisting of only the type
> >column
> >
> >I run statistics on the schema with the estimate option
> >
> >Then I rerun the execution plan with this same query
> >
> >It still indicates Oracle will do a full table scan on the table.
> >
> >Why won't Oracle use the index?
> >
> >Thanks.
> >
>
> how many rows in the table?
> how selective is type?
>
> if not many rows -- full scan.
> if type is not selective -- full scan.
>
> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
> Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
>
> Current article is "Fine Grained Access Control", added June 8'th
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 10 1999 - 14:24:51 CDT