Re: SQL tuning with LIKE expression

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3f701190$0$247$ed9e5944_at_reading.news.pipex.net>...
> "Junaid" <letsconnect_at_hotmail.com> wrote in message
> news:dfb44909.0309220843.1b38843a_at_posting.google.com...
> > Hello,
> > I'm using Oracle 8i and trying to tune a sql statement that has six
> > table joins. I have a numeric indexed column which is using 'LIKE'
> > expression in the where clause. This expression is causing full table
> > scans for all the six joined table. Instead, if I substitute the like
> > with = then the response is almost instataneous.
> >
> > For utility purposes, I have to use the like expression in the where
> > clause. What are my other options to explore to tune the sql
> > statement?
> >
> > I would appreciate any input.
>
> If the data is a number then use appropriate numeric operators =, > between
> etc. If the data is text then store it as text in a character column and use
> text operators.

I have changed the query to use the numeric operators. But the query only uses the associated index if I use the = operator. Using any other numeric operator abandons the use of the index.

Any ideas?

Thanks
Junaid Received on Tue Sep 23 2003 - 11:36:38 CDT