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.

A relational database in general, and DB2 in particular, will not guarantee the order of rows retrieved unless you specify the Order By clause.

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.

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

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

Sathyaram

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