Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Why difference in result

 
Manoj Kumar Jain
Ranch Hand
Posts: 198
Java Linux Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I am using Oralce database and running a query like

This query is fetching me 9 records from the table. I am running this query at two places.
1. On SQL Developer
2. In my Java code by JDBC.

I am getting different records in both of cases, I know this is due to indexing but not sure how.
A little elucidation will be a great help.

Thanks in advance.

 
Tim Moores
Bartender
Posts: 3134
50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is "rownum" an attribute of the table or some system-specific value? If it's an attribute then both queries should return the same values, and indexing should play no role in this.
 
Matthew Brown
Bartender
Posts: 4568
9
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
rownum is an Oracle specific mechanism for returning the first N rows.

Exactly why this is happening, I don't know. But SQL doesn't guarantee a sort order unless you specify one. If you're trying to take the first 9 rows you should always have an ORDER BY clause.
 
Pete Nelson
Ranch Hand
Posts: 147
Debian Eclipse IDE Tomcat Server
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Many SQL tools (SQL Developer, SQL Navigator, etc) optimize your query for you, so when you don't specify columns or ordering, you may not see data come back in the same order.

Also, Oracle ROWNUM is a pseudocolumn providing the row number of the data returned at the time it was returned. So if the ordering changes between queries, and you're only getting back up to the first nine rows, you are very likely to see different results.

The best way to resolve this is to specify the columns you want, and add an ORDER BY clause to your query. Then results should come back the same from JDBC and the various SQL tools.
 
Manoj Kumar Jain
Ranch Hand
Posts: 198
Java Linux Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks all for reply..
Yes order by clause can resolve this problem as we are saying that we need the results in which order. but I am curious to know if we don't mention it then what make it to give different result. Pete can you please elucidate it more.
also when the query is same then the method for optimization should be same. so it should return the same results.

I have googled for it but couldn't find very much.
 
Rob Spoor
Sheriff
Pie
Posts: 20751
68
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you don't specify an explicit ordering using ORDER BY, the database system is allowed to return its records in any unspecified order. Apparently that's exactly what's happening.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic