How to force an index range scan ?

Hello,

I have a performace problem which would be solved if I could force the query optimizer to do a rangescan on a particular index instead of a full index scan.

Example:

BigTable has a primary key PK, a unique index has been built on that key.
SmallOne is a smaller table containing a column which references the PK of bigtable
SmallTwo is another table containing a column also referencing the PK of bigtable

All apropriate index for speeding up queries have been created and tables analyzed.

If I join BigTable with SmallOne to fetch some records of BigTable using a where clause
primary on columns of SmallOne and using the PK and its reference to find the associated bigtable records.
Then with this table the query optimizer uses an index range scan resulting in a response time of approx 1.5 seconds or so

With SmallTwo however the optimizer does a full index scan resulting in a responsetime of over 30 seconds.

Note that both queries on SmallOne and SmallTwo return a set of PK's of BigTable which are similar in size except in one condition where SmallTwo returns
a larger number of PK's but still small enough to prefer an index range scan on the PK index.

Soi I would like to use a query hint to speed up the oracle query by forcing it to use a index range scan Received on Fri Jun 08 2001 - 08:59:00 CDT