Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index not used by query
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--