How to 'force' optimizer to use indexes?

Hi, everyone:

We need to run a query described as follows:

select f1, f2, .., fn
from t1
where f3=f5,
and f2 like '%string%'
order by f2

where f1, f2,..., fn are fields of table t1 (with about 1.2 million records), f2 is the leading column of a concatenated index.

In the past, when a leading % is used in like clause, the index on f2 would not be used. Yet, when I tested the query on the Oracle 8.06 box, a full index scan (using the concatenated index) is used to retrieved rows. When I tested the query on Oracle 8.05 box, it used full table access. It took about 0.5 seconds when using full index scan and over 20 seconds using full table access. Here are my questions:

1. When a leading % is used in like clause, an index can still be used. Is this a new feature of Oracle 8( or 8.06)?
2. How can I 'force' or 'induce' optimizer to use index instead of full table access? For example, I also need to run the following query:

select count(*)
from t1
where f3=f5,
and f2 like '%string%'

I could not get optimizer to use the index on f2 although doing full index scan would improve the performance significantly.

Any of your input or suggestions would be greatly appreciated.

Chang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Mar 07 2000 - 00:00:00 CST