Re: Ordering with nulls first and index usage
- 01.12.2012 1:26, Leyne, Sean wrote:
> In order for an index to be used for ORDER BY, the sort order much match
> exactly the storage order of the index.
> All indexes have NULLs stored at the end (ie. not first).ASC indices have NULLs stored in the beginning, DESC indices have NULLs
stored at the end.
- On 1-12-2012 09:53, Dmitry Yemanov wrote:
> 01.12.2012 1:26, Leyne, Sean wrote:For Firebird 2.x yes, but for Firebird 1.x no:
>> In order for an index to be used for ORDER BY, the sort order much match
>> exactly the storage order of the index.
>> All indexes have NULLs stored at the end (ie. not first).
> ASC indices have NULLs stored in the beginning, DESC indices have NULLs
> stored at the end.
>When I specify ORDER BY A_COLUMN DESC NULLS FIRST, the descending indexHi Marcin!
>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.
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
WHERE A_Column IS NULL),
MyCTE2((MyDescOrder, A_Column) As
(SELECT 1, A_Column
WHERE A_Column IS NOT NULL)
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.
- 01.12.2012 13:02, Mark Rotteveel wrote:
> For Firebird 2.x yes, but for Firebird 1.x no:True, I was speaking about ODS 11 and above.