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

You can not reliably control in what order Oracle retrieve the row of a table without an ORDER BY.

Furthermore, without the /*+APPEND*/ hint, Oracle will store the rows physically in a heap table where there is room, which may not be at the end of the table ! You may think Oracle inserts them sequentially but any DML or concurrent 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 guarantee 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 2

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

Answer 3

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 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.

Answer 5

YES, there IS a way to control your order. I found through experience where I had a table of Countries, we'll call it OLD_COUNTRIES, that looked like this:

-----------------------------------
| ID    |  CODE  |  NAME          |
-----------------------------------
|112099 |  AF    | Afghanistan    |
|112100 |  AA    | Albania        |
|...    |  ..    | ...            |
|112358 |  ZB    | Zimbabwe       |
|112359 |  AZ    | Azores Islands |
|...    |  ..    | ...            |
-----------------------------------

Where I wanted the items that were added after the main list of countries (like the Azores and other islands that were added later) to actually appear in alphabetical order with the rest of the countries when I inserted them into a new table:

CREATE TABLE MYAPP.COUNTRIES
(
    ID NUMBER(*, 0)
  , CODE NVARCHAR2(20)
  , NAME NVARCHAR2(250)
);

I then ran this to make it work:

INSERT /*+ append */ INTO MYAPP.COUNTRIES (ID, CODE, NAME)
SELECT ID, CODE, NAME FROM MYAPP.OLD_COUNTRIES_TABLE ORDER BY NAME ASC;
COMMIT;

And my new COUNTRIES table came through alphabetized by name just fine.

NOTE: COMMIT is required or you'll get the error: ORA-12838: cannot read/modify an object after modifying it in parallel when you try to open the table to look at it in Oracle SQL Developer.

NOTE: If you don't use /*+ append */, it will not insert in the order you specify - it will ignore ORDER BY. And I know that it used my ORDER BY when I used /*+ append */ and not just defaulted to a primary key on the old or new tables because neither had primary keys.

CAVEAT: According to the accepted answer's author, the append hint, as with "any DML or concurrent activity...might produce a different physical organization". While possibly true, don't do any concurrent operations during this and have a COMMIT statement in your code where I do, so that doesn't happen!