Re: how to detect records order in a table with no id, timestamp?
Date: 1997/11/05
Message-ID: <3467a16a.96145890_at_newshost>#1/1
Space management is implemented *very* differently in the databases.
Consider the new 'multiple insertion points' in Sybase. With >1 person inserting into a table, they will use different insertion points into the table (so as to not block eachother as frequently). There are now multiple 'ends of the table'. Definitely using this, you will not get data out in the order you inserted (it will depend on what insertion point you used).
What about clustered tables in MS or Sybase? Don't they sort of fall out in key order, not insertion order?
Previously you mentioned updates as coming out in the 'right' order as well. This was due to non-in place updates taking place. They (MS and Sybase) would delete and insert the row. You happened to see the row at the 'bottom' of the table. What if you followed lots of rules (eg: no triggers, update 1 row only, don't update any indexed columns, and so on) and got them to do an in place update? the row wouldn't move -- it wouldn't be 'last'.
If the newly inserted data always went to the 'end' of the table, you'd be saying that they never re-use space (tables only get bigger, if you delete, space is never reused) or that every delete causes a massive table re-org to defragment space.
There are many ways to see this happen...
Bottom line - regardless of the database product -- don't count on rows coming out in some order without an order by clause.
On 4 Nov 1997 23:56:54 GMT, lesliet_at_u.washington.edu (L. Tseng) wrote:
>Thomas,
>
>Thanks for the reply.
>I know this is true for Oracle, but do you know how this randomness would
>occor under MS SQL? I just could not generate an incident on MS SQL Server
>to prove what you said. (I insert and delete on a table hundreds thousands
>times on MS SQL). Is select implemented differently between Oracle and SQL Server?
>
>Thanks,
>
>In article <346139c6.4077763_at_newshost>,
>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>>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.
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 Wed Nov 05 1997 - 00:00:00 CET