aspose file tools*
The moose likes JDBC and the fly likes SELECT QUERY Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SELECT QUERY" Watch "SELECT QUERY" New topic
Author

SELECT QUERY

Sub swamy
Ranch Hand

Joined: Oct 02, 2002
Posts: 121
If we issue the statement

select * from <tableName>

in what order will the rows be returned 1) if there is a primary key and 2) if we dont have a primary key/unique key
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

if you don't provide a specific order then it is up to the database. There are no guarantees.
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
From my experience mostly the order is the time that the different records in a table are updated.

You should consult you database information for that.


Remko (My website)
SCJP 1.5, SCWCD 1.4, SCDJWS 1.4, SCBCD 1.5, ITIL(Manager), Prince2(Practitioner), Reading/ gaining experience for SCEA,
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

it can come out in this order, but to assume it comes out in this order without providing an explicit ordering would be a mistake.
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3355

I think what David said is correct.

By default the database creates an index on a key column (Primary Key). Its up to the programmer to create an index on a non-key column.

The presence of keys in a table does not matter for the ordering of retrieval. Until and unless the ordering is mentioned in the SELECT statement it is NOT considered.

As the database maintains the rows in "heap-ordered" and NOT "index-ordered" and if you dont specify any specific ordering in the SELECT statement, the ordering is purely upto the database. Its not guaranteed!

Hope this helps!
[ August 10, 2007: Message edited by: Raghavan Muthu ]

Everything has got its own deadline including one's EGO!
[CodeBarn] [Java Concepts-easily] [Corey's articles] [SCJP-SUN] [Servlet Examples] [Java Beginners FAQ] [Sun-Java Tutorials] [Java Coding Guidelines]
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
You're right always try to have an explicit order, but when I don't provide one my customers always complain that there is no order and I always see that the records are ordered in a way they are updated.

This behavior could arise by the use of temp tables in my stored procedures and where mostly is no index available.

I did a test for a direct select in Sybase, Mysql and Oracle and could see that indeed by defining a primary key the records are ordered that way. I think this is because for the database is more natural to retrieve data ordered by their primary key.
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3355

Originally posted by Remko Strating:
I think this is because for the database is more natural to retrieve data ordered by their primary key.


Though it could possibly be tentatively-correct but not exactly. Because the data in the *physical file* is retrieved in the order they reside in the blocks. But we can't take it for granted that it is guaranteed.

In precise, simply having a PK defined for the table really does not matter until its used in the order by column in the SELECT clause to have a real effect.
[ August 14, 2007: Message edited by: Raghavan Muthu ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SELECT QUERY