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

Answer 2

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.