Home » SQL & PL/SQL » SQL & PL/SQL » Clarification on 'N' th row
Clarification on 'N' th row [message #249827] Fri, 06 July 2007 01:26 Go to next message
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 #249829 is a reply to message #249827] Fri, 06 July 2007 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why is it that the order of rows fetched is the same?

Wrong. I posted an exemple in why is it that the order of rows fetched is the same?

Regards
Michel
Re: Clarification on 'N' th row [message #249831 is a reply to message #249829] Fri, 06 July 2007 01:42 Go to previous messageGo to next message
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 #249834 is a reply to message #249831] Fri, 06 July 2007 01:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I call it coincidence. It just happens to be that way in your case.

But also consider this: Oracle parses a query, the optimizer chooses an access path. The second time you execute the query, Oracle reuses the execution plan. If not much has changed, Oracle's Optimizer will choose the same access path. Perhaps a little vague, but I don't know how to explain it more clear.

MHE
Re: Clarification on 'N' th row [message #249838 is a reply to message #249834] Fri, 06 July 2007 02:15 Go to previous messageGo to next message
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 Go to previous message
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 here
Use 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

Previous Topic: how can we find characters in string without using substr?
Next Topic: Computed columns in Oracle
Goto Forum:
  


Current Time: Fri Dec 09 23:31:34 CST 2016

Total time taken to generate the page: 0.12000 seconds