Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
Hi Tony,
When you store rows in a relational database, the order that are
inserted need not be the order that they are returned in. That is the
joy/purpose of RDBMS.
If you absolutley must have the rows returned to you in some order, then
you must have a column in each row upon which you do an 'order by' on
your selects.
Because Oracle will reuse space in a block from a deleted row, you might find that you get rows 1, 2, 3, 4, 9, 10, 7, 8 if the two rows 5 and 6 were deleted before rows 9 and 10 were insterted if you do not specify an order clause in your query.
As for what happens behind the scenes, I'm afraid that as a developer (or dba) then is *is* your concern - this is why so many ports of applications from SQLServer to Oracle (or the other way) just don't work - the developers 'know' how to do things in one RDBMS and try to apply those parctices to the others - result, failure.
Cheers,
Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: tonyz.wrightz_at_consultant.com (Tony Wright)
[mailto:tonyz.wrightz_at_consultant.com]
Posted At: Wednesday, October 02, 2002 6:48 AM
Posted To: server
Conversation: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT
Statement
Subject: Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT
Statement
But how would this all effect my desire to retrieve an ordered subset of records?
Surely the only things that I would need to worry about is that the order is consistent ie. record 1 is before record 2 is before record 3 etc; <SNIP>
Whatever happens behind the scenes is not really my concern, surely? <SNIP> Received on Wed Oct 02 2002 - 03:50:55 CDT