>When I specify ORDER BY A_COLUMN DESC NULLS FIRST, the descending index
>created on this column is not used and I get 'natural' in plan.
>Is there a possibility to use some index when 'nulls first' is specified.

Hi Marcin!

I think it may be possible to force using an index, but you have to change your query a bit:

With MyCTE1(MyDescOrder, A_Column) As
(SELECT 2, A_Column
FROM A_Table
WHERE A_Column IS NULL),
MyCTE2((MyDescOrder, A_Column) As
(SELECT 1, A_Column
FROM A_Table
WHERE A_Column IS NOT NULL)

SELECT *
FROM MyCTE1
UNION
SELECT *
FROM MyCTE2
ORDER BY MyDescOrder DESC, A_Column DESC

I didn't have any table with descending index to test available, but a similar query with ascending indexes (of course trying to put the NULLS LAST, showed that my index was used.

Having said that, I don't know whether this will be any quicker than going NATURAL, and given that it complicates the query a bit, I'd say that even if it did, performance would have to be crucial for you to consider this.

Set