Re: Order of rows inserted into table differing from order when selected

Isn't that why sql has an order by statement. To the best of my knowledge, no relational database guarantees the order in which it stores the data.

As for Oracle, I assume that its just re-using space freed up by deleted rows. Others may be able to give you a more technical reason.

Ken Ho wrote in message <3b014cd3.6675272_at_news.harvard.edu>...
>Situation:
>
>Oracle 7.3.4.5.2
>
>A table called ORDERS with a single column, ID, which is repeatedly
>loaded and periodically emptied using DELETE FROM ORDERS (typically
>but not always with a WHERE clause which works from a cursor to delete
>all rows), sometimes with small quantities of rows (e.g., 10) and
>sometimes with larger quantities (e.g., 1000).
>
>If I load an ID of 999 into the table, COMMIT, then load an ID of 111
>and COMMIT, I need to preserve that order (999 first, 111 second) when
>I select rows from the table, which is done with SELECT ID FROM
>ORDERS.
>
>I have found that the desired order is not ***guaranteed*** (this
>showed up when I was doing repeated loading and deleting tests with
>around 650 rows), although the order was preserved when I used
>TRUNCATE TABLE ORDERS instead of DELETE FROM ORDERS. It looks like
>the best solution would be to add another column which gets its value
>from a trigger using a strictly increasing sequence number, then when
>selecting rows from the table, always ordering rows by the sequence
>number column value (SELECT ID FROM ORDERS ORDER BY SEQUENCE_NUM), but
>I would appreciate hearing better solutions.
>
>Can someone point me to the Oracle or SQL-related reason why the order
>is not preserved when using DELETE FROM ORDERS, successive INSERT +
>COMMIT command pairs for each row, and SELECT ID FROM ORDERS? It
>struck me as counterintuitive that INSERT and COMMIT for each row
>wouldn't produce the same order when using SELECT ID FROM ORDERS
>afterward.
>
>-Ken Ho
>hoke_at_gse.harvard.edu
Received on Tue May 15 2001 - 11:22:29 CDT