| Clarification on 'N' th row [message #249827] |
Fri, 06 July 2007 01:26  |
diku
Messages: 23
Registered: April 2007
|
Junior Member |
|
|
Hi Folks,
I just need a conceptual clarification on a pretty old thread-the one about there being NO such thing as 'n' th row in a RDBMS.
The link in the form is:http://www.orafaq.com/forum/m/232438/104270/?srch=nth+row#msg_232438
Herein I also found a link to Tom Kyte which specifically and elegantly answered all my doubts.But I still don't get something.
Unless and until the user EXPLICITLY gives a ORDER criteria there is no distinction between two rows.Then every time I do a SELECT * FROM ANY_TABLE why is it that the order of rows fetched is the same? Has my DBA set something as default or is this some internal setting? I tried running simple SELECT s on multiple tables over a period of time and don't see any difference in the ORDER of record retrieval.I'm just a beginer so don't have much idea.Can someone please point out something which I missed(in the forum) in which case aplogies
Thanks,
Diku
|
|
|
|
|
|
| Re: Clarification on 'N' th row [message #249831 is a reply to message #249829] |
Fri, 06 July 2007 01:42  |
diku
Messages: 23
Registered: April 2007
|
Junior Member |
|
|
Ya Michel. I did go through your post and it DOES SHOW diffrential ordering for the 26 rows.
I just wanted to know in case for me it's ALWAYS the same order is it because some default setting/server config or what?
Not that this holds up some urgent work, I just wonder WHY?
Appreciate your effort. Thanks
Diku
|
|
|
|
|
|
| Re: Clarification on 'N' th row [message #249838 is a reply to message #249834] |
Fri, 06 July 2007 02:15 |
diku
Messages: 23
Registered: April 2007
|
Junior Member |
|
|
I think I get your point.
But as far as my limited knowledge goes the optimiser would come into the picture if I have things like Primary Keys/Indexes etc. Just out of curiousity I did a test case.
Without any primary key/index I created a one-column table and inserted about 50 records and tried displaying data through multiple sessions,through different user ids also. And well, the result was always the same.
As you put it MAAHER, could just be coincidence.
Strange one though.
Thanks,
Diku
|
|
|
|
| Re: Clarification on 'N' th row [message #249887 is a reply to message #249838] |
Fri, 06 July 2007 07:27 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If nothing changes in your database, then you can reasonably expect the order of the rows returned to be the same when you exectue the same query twice. (I'd say it's a certainty, but a years posting here has taught me that that is just begging for trouble).
In real life, things don't stay the same - indexes get added, statistics get regenerated and things do change.
While these things will rarely affect the order of the rows, it is very important to remember that Oracle say hereUse the ORDER BY clause to order rows returned by the statement.
Without an order_by_clause, no guarantee exists that the same query executed more than once
will retrieve rows in the same order. [Updated on: Fri, 06 July 2007 07:28] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
