Re: how to detect records order in a table with no id, timestamp?
Date: 1997/11/04
Message-ID: <346139c6.4077763_at_newshost>#1/1
[Quoted] On 3 Nov 1997 23:41:57 GMT, lesliet_at_u.washington.edu (L. Tseng) wrote:
>Is it possible to order an oracle table output when table does not have
>any id and timestamp info?
>
>In MS SQL Server and Sybase, it seems the record just got inserted or updated
>always comes out the last in select.
>
>Thanks for the help in advance,
>
>Leslie
No, it is not (and even in Sybase and MS SQL Server -- its not). Database tables have no implicit 'order' to them. They are managed as heaps. If you insert 50 rows, delete 1 and insert another -- you may first the last inserted row is the 'first' row or one in the middle or the 'last row'. It is rather random.
The behaviour you observed in Sybase and SQL Server on insert (inserting into the last page) might not always occur and the update behaviour you saw was depending on the fact that most times an update in these databases is actually a DELETE followed by an INSERT. In cases where they could do an in place update, the row would not move and you would not see this behaviour (so don't count on it with them as well -- it doesn't have to happen that way).
If you absolutely need to know the 'order' of records, you'll need to add a column and maintain it with a trigger in an before insert or update trigger.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.
I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Tue Nov 04 1997 - 00:00:00 CET