Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72

    Unanswered: Order of records...

    Can any one tell in what order the data will be retrieved for a SELECT query from a table with the no of records in millions in that table.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A relational database in general, and DB2 in particular, will not guarantee the order of rows retrieved unless you specify the Order By clause.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2003
    Location
    Johannesburg, South Africa
    Posts
    18

    Talking DB2 Information

    Hi

    Not many people know or realise that the data with in a table will automatically lie in the oprder of the CLUSTERING index by default. If you do not specify a CLUSTERING index then rows will be in the order of the FIRST INDEX created.

    Thus if you are getting data through a SELECT * in the incorrect order, this will be the problem.

    What the other replier said is true, if you want to retrieve data in a different order then you will need to use an ORDER BY, but just remember there is move overhead fro this.

    The best solution is to define a CLUSTERING index (only one on a table because of the row sequece) and then other indexes for the other query or use ORDER BY.

    JUST REMEMBER THERE IS ALWAYS A COST. Lots of Indexes will cause UPDATES/INSERTS and DELETES to be slower.
    Divvy

  4. #4
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96

    Re: DB2 Information

    Originally posted by tupiex
    Hi

    Not many people know or realise that the data with in a table will automatically lie in the oprder of the CLUSTERING index by default. If you do not specify a CLUSTERING index then rows will be in the order of the FIRST INDEX created.

    Thus if you are getting data through a SELECT * in the incorrect order, this will be the problem.
    Hi,
    AFAIK it's even not sure you will get the data in the order of the clustering index or the first index created. If DB2 is using parallel IO to get the data it can be returned to the DB2 engine in a different order and without an order by it's directly returned to the requesting application.

    There's also a difference between DB2 OS/390 and DB2 Unix/Windows when talking about clustering indexes. DB2 Unix/Windows tries to put the data in the right phisical order but you can't be sure it really is.
    If I remember right from 10 years ago DB2 OS/390 fysically inserts the rows in the order of the clustering index.

    Kind regards, Gert

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Information

    yep ... even if you get the rows in the same order a million times, it is merely coincidence

    Sathyaram

    Originally posted by GertK
    Hi,
    AFAIK it's even not sure you will get the data in the order of the clustering index or the first index created. If DB2 is using parallel IO to get the data it can be returned to the DB2 engine in a different order and without an order by it's directly returned to the requesting application.

    There's also a difference between DB2 OS/390 and DB2 Unix/Windows when talking about clustering indexes. DB2 Unix/Windows tries to put the data in the right phisical order but you can't be sure it really is.
    If I remember right from 10 years ago DB2 OS/390 fysically inserts the rows in the order of the clustering index.

    Kind regards, Gert
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there's no clustering index, rows might be returned in the order of the first index, but what if the first index isn't declared until after the table has been loaded? what then?

    unless you use ORDER BY, any sequence you see is, as they say, mere coincidence


    rudy

Posting Permissions

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