This week's book giveaway is in the General Computing forum. We're giving away four copies of Arduino in Action and have Martin Evans, Joshua Noble, and Jordan Hochenbaum on-line! See this thread for details.
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.
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.
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.
OCPJP
In preparing for battle I have always found that plans are useless, but planning is indispensable. -- Dwight D. Eisenhower
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.
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.
0
