aspose file tools*
The moose likes JDBC and the fly likes Keyed file still produces fifo results Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Keyed file still produces fifo results" Watch "Keyed file still produces fifo results" New topic
Author

Keyed file still produces fifo results

Bacon
Ranch Hand

Joined: Jul 14, 2004
Posts: 305
I'm executing a SQL statement over a keyed file, yet the results come out in FIFO. Do I have to specify an "order by" clause? If so, what's the point of accessing a keyed file/table?

Here is the code that build and executes the SQL statement.

// Run an SQL SELECT statement
Statement stmt = con.createStatement ();
String SQLStmt =
"SELECT CUSNUM,LSTNAM,INIT,STREET,CITY,STATE,ZIPCOD,CDTLMT,CHGCOD,BALDUE,CDTDUE FROM RAYLIB.QCUSTCDT01 WHERE " + SqlWhereClause;
ResultSet rs = stmt.executeQuery (SQLStmt);

Thanks,
Dmitry Melnik
Ranch Hand

Joined: Dec 18, 2003
Posts: 328
Do I have to specify an "order by" clause?

I think so.

If so, what's the point of accessing a keyed file/table?

On large tables "order by" works much faster when you order a table by "keys".
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Just a tiny addition to Dmitry's great answer:
Originally posted by Ray Marsh:
Do I have to specify an "order by" clause?
If you want results in a specific order, you have to ask for it. Full stop.

If you don't specify an "order by", the database optimiser is free to execute the query in whatever way it likes, and return the result in whatever order it happens to read the rows in. Most of the time this will be some "obvious" order, but don't ever rely on it. A change in the amount or nature of your data may prompt the optimiser to use a different execution plan and return results in a different order. Similarly, you might add a new index to the table as part of an application change and find the result order changing. An upgrade to the database may include optimiser tweaks that have the same effect. And, last but certainly not least, when read by human eyes your SQL code is not a clear expression of your intent.

All in all this is a recipe for maintenance nightmares. If you want results in a specific order, always use an "order by".

- Peter
[ July 31, 2004: Message edited by: Peter den Haan ]
Bacon
Ranch Hand

Joined: Jul 14, 2004
Posts: 305
Thanks for the info.

I'm used to an environment where the DB file accessed always produces the data in key order, unless otherwise specified. This is a change of perspective for me.
 
jQuery in Action, 2nd edition
 
subject: Keyed file still produces fifo results