Order of rows inserted into table differing from order when selected

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 - 10:54:31 CDT