Re: How to force an index range scan ?

Have you looked at using a hint like :

select /*+ INDEX mytable myindex */ * FROM mytable WHERE ...

Hth

Ted

On 8 Jun 2001 06:59:00 -0700, gerritcap_at_hotmail.com (Gerrit Cap) wrote:

>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

EMail: knijff_at_bigfoot.com Received on Sat Jun 09 2001 - 13:04:49 CDT