Hello,

A SQL optimization question...

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).
So, I tried to hint the optimizer that way

select saleprice
from presence
where productID = 1959201 and valuedate = '2004-04-01'
order by saleprice + 0

This works ok, the optimizer chooses the right indice now.

The pb comes if I want to do some groupings :

select saleprice , count(saleprice)
from presence
where productID = 1959201 and valuedate = '2004-04-01'
group by saleprice
order by saleprice

Then, I don't know how to trick the optimizer so that it picks the best
indice (not the one on SalePrice).

Can anyone help ?

Thanks.

--
Jerome