Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL TUNING
Vinay Joshi wrote:
> Hi,
> I am trying to tune a sql statement on Oracle 7.3.3 Unix.
>
> The table a has 10,000 records.
> It has a uniq index on reference column.
>
> Here is the query:
> select /*+ index(a a_ref) */
> rowid
> from a
> where substr(reference,1,7) = 'Hithere';
>
> I know the plan returns a full table scan because I am using a function
> (substr). Is there any way of getting round this problem so it uses the
> index ?
>
> Many thanks...
Why use a substr() function?? What you really want is a LIKe operator, which will use the index. Change you query to read:
select rowid
from a
where reference LIKE 'Hither%';
this does exactly the same thing as you query does only faster.
Enjoy.
ed
Ed Prochak
Magic Interface, Ltd.
Cleveland Ohio USA
1-440-498-3702
Received on Fri Nov 20 1998 - 14:45:00 CST