Oracle Insert Select with order by

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)

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.

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.