Topic
  • 2 replies
  • Latest Post - ‏2012-07-07T22:35:15Z by Shin.O
Shin.O
Shin.O
2 Posts

Pinned topic Sort before/after join

‏2012-07-07T01:07:59Z |
Hi,
Anyone please help me understand whether below two queries always bring the same results?

T1: OrderNum (Unique Key), Customer
T2: Customer (Unique Key), CustName

Q1
select OrderNum, Customer, CustName from
(select OrderNum, Customer from T1 where OrderNum between 1 and 999 order by OrderNum)
left join T2
on T1.Customer = T2.Customer

Q2
select OrderNum, Customer, CustName from
(select OrderNum, Customer from T1 where OrderNum between 1 and 999)
left join T2
on T1.Customer = T2.Customer
order by OrderNum
Updated on 2012-07-07T22:35:15Z at 2012-07-07T22:35:15Z by Shin.O
  • B.Hauser
    B.Hauser
    253 Posts

    Re: Sort before/after join

    ‏2012-07-07T09:16:10Z  
    It's the query optimizer who decides which access paths (Key Constraints, Indexes or Keyed logical files) will be used, by estimating all access paths and interviewing the statistics.
    The main goal is to get the data returned as fast as possible.
    Sometimes it is faster to use an access path which does not match the Order by clause, store the result in a temporary object and sort the data in the temporary object according the order by clause.

    There is a rule:
    If you need the data returned in a specific order, add an order by clause.
    If you do not need the data returned in a specific order, do not add any order by clause.

    For more information about query optimization and indexing strategies, I'd warmly suggest to read the followin white paper:
    IBM DB2 for i
    indexing methods and strategies
    https://www-304.ibm.com/partnerworld/wps/servlet/download/DownloadServlet?id=G_mhBFTHFjyiPCA$cnt&attachmentName=IBM+DB2+for+i+indexing+methods+and+strategies.pdf&token=MTM0MTY1MjY0MTA4MA==&locale=en_ALL_ZZ

    Birgitta
  • Shin.O
    Shin.O
    2 Posts

    Re: Sort before/after join

    ‏2012-07-07T22:35:15Z  
    • B.Hauser
    • ‏2012-07-07T09:16:10Z
    It's the query optimizer who decides which access paths (Key Constraints, Indexes or Keyed logical files) will be used, by estimating all access paths and interviewing the statistics.
    The main goal is to get the data returned as fast as possible.
    Sometimes it is faster to use an access path which does not match the Order by clause, store the result in a temporary object and sort the data in the temporary object according the order by clause.

    There is a rule:
    If you need the data returned in a specific order, add an order by clause.
    If you do not need the data returned in a specific order, do not add any order by clause.

    For more information about query optimization and indexing strategies, I'd warmly suggest to read the followin white paper:
    IBM DB2 for i
    indexing methods and strategies
    https://www-304.ibm.com/partnerworld/wps/servlet/download/DownloadServlet?id=G_mhBFTHFjyiPCA$cnt&attachmentName=IBM+DB2+for+i+indexing+methods+and+strategies.pdf&token=MTM0MTY1MjY0MTA4MA==&locale=en_ALL_ZZ

    Birgitta
    Thanks Birgitta!
    I understood I need to go with Q2, of my example, if I need the result always returned in order of OrderNum.