Oracle Insert Select with order by

Question

I am working on a plsql procedure where i am using an insert-select statement. I need to insert into the table in ordered manner. but the order by i used in the select sql is not working.

is there any specific way in oracle to insert rows in orderly fashion?

Answer 1

The standard Oracle table is a heap-organized table. A heap-organized table is a table with rows stored in no particular order.

Sorting has nothing to do while inserting rows. and is completely pointless. You need an ORDER BY only while projecting/selecting the rows.

That is how the Oracle RDBMS is designed.

Answer 2

I'm pretty sure that Oracle does not guarantee to insert rows to a table in any specific order (even if the rows were inserted in that order).

Performance and storage considerations far outweigh ordering considerations (as every user might have a different preference for order)

Why not just use an "ORDER BY" clause in your SELECT statement?

Or better yet, create a VIEW that already has the ORDER BY clause in it?

CREATE VIEW your_table_ordered
SELECT *
FROM your_table
ORDER BY your_column

Answer 3

The typical use case for an ordered insert is in order to co-locate particular value in the same blocks (effectively reducing the clustering factor on indexes on columns by which you have ordered the data).

This generally requires a direct path insert ...

insert /*+ append */ into ...
select ...
from   ...
order by ...

There's nothing invalid about this as long as you accept that it's only worthwhile for bulk data, that the data will load above the high water mark only, and that there are locking issues involved.

Another approach which achieves mostly the same effect, but which is more arguably more suitable for OLTP systems, is to create the table in a cluster.

Answer 4

The use of an ORDER BY within an INSERT SELECT is not pointless as long as it can change the content of the inserted data, i.e. with a sequence NEXTVAL included in the SELECT clause. And this even if the inserted rows won't be sorted when fetched - that's the role of your ORDER BY clause in your SELECT clause when accessing the rows.

For such a goal, you can use a work-around placing your ORDER BY clause in a sub-query, and it works:

INSERT INTO myTargetTable
(
  SELECT mySequence.nextval, sq.* FROM
    (   SELECT f1, f2, f3, ...fx 
          FROM mySourceTable
         WHERE myCondition
      ORDER BY mySortClause
    ) sq
)