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.

Thanks
Junaid

My gues is that the reason is caused by implict typeconversion on the
database field.
The numeric field is converted imlisitly to string and that prevents the use
of index.
Don't use LIKE operator on numeric fields.

On 22 Sep 2003 09:43:22 -0700, letsconnect (AT) hotmail (DOT) com (Junaid) wrote:

No, you don't. There is no reason to use LIKE on a number column.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

"Junaid" <letsconnect (AT) hotmail (DOT) com> wrote

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.


--
Niall Litchfield
Oracle DBA
Audit Commission UK

Sybrand Bakker <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote

I know I don't have to but our shared client application's framework
is forcing us to use it, for the time. Also, if I use numeric operator
for this number column, only = utilizes the index on this column. If I
use < or > then the associated index is not used.

Any ideas?


Thanks for the response.
Junaid

"Junaid" <letsconnect (AT) hotmail (DOT) com> wrote

As far as numeric operators are concerned
a) Do you have up to date stats? and
b) what percentage of the table are you expecting to return?

As far as being forced to use like then the framework is forcing you to
utilize a FTS.


--
Niall Litchfield
Oracle DBA
Audit Commission UK

"Niall Litchfield" <n-litchfield (AT) audit-commission (DOT) gov.uk> wrote

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

How is your data distributed? I.e. how many distinct values you have
compared the to the row count.
How many rows of the total row count of table the query is returning?

I guess that you are using a cost based optimizer.

Optimizer decides to use full table scan if it sees that there is no use to
use the index. I.e. the full table scan is faster.

You can help the optimizer to take right decisions by running analysis on
your database.
If you know that the use of index is faster, then you may force the use of
index by giving a hint in sql sentence.

Quick check may be to give hint /*+ FIRST_ROWS */ in sql sentence, it
usually increases the use of indexes.

letsconnect (AT) hotmail (DOT) com (Junaid) wrote in message news:<dfb44909.0309230836.2fc9ef05 (AT) posting (DOT) google.com>...
I guessing what you want here because you say NOTHING about the
purpose/intention of your query.

<mind reader mode on>
From the comments about the LIKE operator, and the desire to use < and

If I read your mind corrrectly, then the solution is to use BOTH > and
<. Better yet use the BETWEEN AND operator. This will use the index in
RANGE SCAN mode. You still need to assure that statistics are up to
date enough to make the optimizer happy.

<mind reader mode off>

As always, there is no better solution than thoroughly understanding
your tools and your goals.

HTH,
Ed Prochak