kokok_kokok wrote:

> If I
> If the index is (a,b) then
>
> "select * from foo order by a, b" uses the index in the plan, but what I need is:
>
> "select * from foo order by a desc, b"
>
>
> How can I create a index to be used in the above sql statement?
>

You can't. Nor should you want to. Everybody with even a vague
recollection of a computer science course knows that the cost of
a sort is nlog(n) while reading from an index is linear with the
number of records to be read. However the cost of reading records
from the disk in index order - random relative to disk placement -
overwhelms the cost of the sort. If you're reading a whole table,
its much faster to read it in disk order and sort the result rather
that rattle the disk around finding a record here, a record there.

The exception is a query with a limit/first clause - but even there
an index on A and a sort on B shouldn't be horribly expensive. If
it is, then store a pseudo field that's like B, but inverted.
That's what our descending indexes are.

Good luck,

Ann