INSERT with ORDER on Oracle

Question

On Oracle 10g we need to insert records from a view into a table to support a dumb client application that does not have sort or ORDER options itself. Is there any way to control the order in which our INSERT statement adds records to the destination table?

Answer 1

Just use an ORDER BY. Something like

INSERT INTO table
(
SELECT 
      column1, column2
FROM 
      view
ORDER BY 
      column2
)

EDIT, this won't work actually. You could create a temporary view with the rows in the appropriate order, then do the insert.

Answer 2

Hi Stuart,

you can not reliably control in what order Oracle stores the rows physically in a heap table (the default table organization). You may think Oracle inserts them sequentially but any DML or conccurent activity (2+ sessions inserting) might produce a different physical organization.

You could use an INDEX ORGANIZED table to store the rows in the order of the PK. Most simple queries thereafter on that table will produce a sorted set of rows. This would not however garantee that oracle will select the rows in that order if you don't specify an ORDER BY (depending on the query and the access path, the rows may come in any order).

You could also use a view with an order by, this is probably your best bet if you can't touch the application (rename the table, create a view with the name of the table, let the application think it queries the table). I don't know if it is feasible in your case.

Answer 3

Unless you specify an ORDER BY, you can never guarantee the order in which Oracle will return rows from a SELECT

Answer 4

Your main problem, that of your application not adding an ORDER BY to its query, might possibly be solved by the use of an index on the column(s) you wish to order by, then use a stored outline to cause the query to use the index to access the table.

You'd have to test to see if this will work or not - be aware that it is possibly not enough to just add an INDEX() hint because the optimiser may find a way to honour the hint while not accessing the index in the right order; and if the query joins to other tables the ordering might be lost anyway.