Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    42

    Unanswered: Is result sorted when index-only access?

    When a query is processed using index-only access, will the result set be sorted based on the index sequence or do I still need to specify an "order by"?

    table:
    empid
    last
    first
    salary


    unique index:
    empid asc
    last asc


    query:
    select empid, last from table where empid between 1000 and 2000


    Will result be sorted by empid+last?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Per relational semantics, you always need to specify an ORDER BY if that is what you want otherwise there is no guarantee that the rows will always be returned in the desired order. While an ORDER BY may not be necessary in some cases, that could change in future, including if the number of rows in the table changes and DB2 chooses a different access plan.

    If DB2 can avoid a sort by using index only access, then specifying ORDER BY will not cause an additional sort to happen because of the ORDER BY. DB2 is smart enough to know when a sort is not needed even when an ORDER BY is specified, so there is no reason to omit the ORDER BY.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •