> Jerome wrote,

> The following query operates on a table containing millions of rows. The
> where clause outputs a few dozen rows at most.
>
> select saleprice
> from presence
> where productID = 1959201 and valuedate = '2004-04-01'
>
> Having an indice on ProductID+ValueDate, this is very quick.
>
> But if I want to sort the results as follows :
>
> select saleprice
> from presence
> where productID = 1959201 and valuedate = '2004-04-01'
> order by saleprice
>
> The optimizer wants to use the indice that exists on Saleprice. What is
> highly inefficient in this case (several minutes to return).

Hi Jerome

When the optimizer throws me curve balls I normally resort to the
following - I hope it can sort your problem out too.

Prepare the 1st query and make a note of the plan generated. Add that plan
to the second query to override the optimizer plan.

For example, if the first query generates the plan (yours will obviously use
the real index names and look different)
PLAN (PRESENCE INDEX (IDX_PRODID_DATE))

change your problem query to

select saleprice
from presence
where productID = 1959201 and valuedate = '2004-04-01'
PLAN (PRESENCE INDEX (IDX_PRODID_DATE))
order by saleprice

Regards
Gary