Re: simple query not using index

        What percentage of the rows in your table have a user id of 1? If more than 25% the index won't be used. If between 10% and 25% the index won't be used if the optimizer "thinks" it would be faster to do a full table scan.

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 4/13/00, 6:16:57 PM, Otis Gospodnetic <otis_at_my-deja.com> wrote regarding simple query not using index:

> Hi,
 

> I'm trying to make this simple query user the index I created, but it
> just doesn't want to :)
 

> I have a table 't' with a column 'user id' that is a foreign key.
> The query that I want to use the index is:
 

> SELECT * FROM t WHERE user id

 

> Using 'set autotrace on' I can see that the table 't' is being fully
> scanned and that the index I created is not being used:
 

> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT OptimizerOOSE (CostT Card87 By
 tes!
> 672)
 

> 1 0 SORT (ORDER BY) (CostT Card87 Bytes!672)
> 2 1 TABLE ACCESS (FULL) OF 'T(CostG Card87 Bytes
1672)

> I created my index like this:
 

> CREATE INDEX my i ON t(user id);
 

> I also did:
 

> ANALYZE TABLE t COMPUTE STATISTICS
 

> Does anyone know why the above simple query is not using the index I
> created?
 

> Thank you,
 

> Otis

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Apr 14 2000 - 00:00:00 CDT