Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Suppose I have a table containing the following data:
Name | Things
-------------
Foo | 5
Bar | 3
Baz | 8
If I want to insert a row, so that the final state of the table is:
Name | Things
-------------
Foo | 5
Qux | 6
Bar | 3
Baz | 8
Is this possible?
I understand we don't typically rely on the order of rows in a table, but I've inherited some code that does precisely that. If I can insert to a location, I can avoid a significant refactor.
The code you have inherited is broken - and it always has been broken. That re-factoring you'd like to avoid needs to be done. There is no alternative to an explicit order by to guarantee the sort order of a result set and there never has been.
Who knows if the code you inherited always returned rows in the order the original developer 'expected' or not.
No, it's not possible as a theoretical matter since, as you say, the order in which rows are returned is arbitrary without an ORDER BY. There is no guarantee that the order will be stable over time.
No, it's not generally possible as a practical matter. However, there may be a couple of workarounds that you could employ.
You could rename the table to <<TABLENAME>>_OLD, create a view on top of the table named <<TABLENAME>>, and embed an ORDER BY in that view that returns the data in whatever order you want.
If the view option doesn't work (and it may be problematic if you've got a lot of data) and you're really, really, really in need a hack, you could create a new table, insert FOO (and all other rows that are before the row you want to add), insert the new row, and then insert the remaining rows. Then drop the old table and rename the new table to use the old table name. No guarantees, of course, but that would generally cause an ORDER BY-less SELECT to return the data in the order you wanted. Terribly ugly if you have a reasonable amount of data in the table.
It depends if, in your actual data, there is any sort of ordering. If there is, you may be able to achieve this by using an index-organized table. But as the others have said, databases are not designed to implicitly guarantee an ordering.
create table my_table (id integer primary key, dummy char(1000)) organization index; insert into my_table (id, dummy) select level*2, 'A' from dual connect by level<40; commit; insert into my_table (id, dummy) values(0, 'A'); select * from my_table; first id returned: 56. The fast full scan does not follow the b-tree - it scans the blocks sequentially as they appear on the disk.
– Jack Douglas
May 20 '11 at 10:04
