At 02:22 PM 24/08/2004 +0200, you wrote:

>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 ?

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

or this might work:

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

or some combination...

./helen