Loading ...
Sorry, an error occurred while loading the content.

Re: Ordering with nulls first and index usage

Expand Messages
  • Dmitry Yemanov
    ... Correct. ... ASC indices have NULLs stored in the beginning, DESC indices have NULLs stored at the end. Dmitry
    Message 1 of 6 , Dec 1, 2012
    • 0 Attachment
      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.

      Correct.

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


      Dmitry
    • Mark Rotteveel
      ... For Firebird 2.x yes, but for Firebird 1.x no: http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/nullguide-sorts.html --
      Message 2 of 6 , Dec 1, 2012
      • 0 Attachment
        On 1-12-2012 09:53, Dmitry Yemanov wrote:
        > 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.
        >
        > Correct.
        >
        >> 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.

        For Firebird 2.x yes, but for Firebird 1.x no:
        http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/nullguide-sorts.html
        --
        Mark Rotteveel
      • Svein Erling Tysv√¶r
        ... 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,
        Message 3 of 6 , Dec 1, 2012
        • 0 Attachment
          >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
        • Dmitry Yemanov
          ... True, I was speaking about ODS 11 and above. Dmitry
          Message 4 of 6 , Dec 1, 2012
          • 0 Attachment
            01.12.2012 13:02, Mark Rotteveel wrote:

            > For Firebird 2.x yes, but for Firebird 1.x no:
            > http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/nullguide-sorts.html

            True, I was speaking about ODS 11 and above.


            Dmitry
          Your message has been successfully submitted and would be delivered to recipients shortly.