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?

Table are sets ,there is no order in a table unless you retrieve the data with an order by — Mihai, Nov 21 '15 at 08:22
sorry Mihai, couldn't understand you. I retrieved the data using order by clause only. but still the insert is in a random order — lourdh, Nov 21 '15 at 08:30
Rows in a table are ***not*** ordered. You **have** to use an `order by` when you select the rows from the table you inserted into. The order by in the `insert` statement is essentially useless — , Nov 21 '15 at 08:32
Mr Pedantic Man Says: Ordering on insert is not _always_ useless. In the case of some classes of table compression the ordering during a `insert /*+ APPEND */ ...` can have a dramatic affect on how much compression happens. Still, in that case, the order of insert does not necessarily determine order on `select`. — Shannon Severance, Nov 21 '15 at 09:05
@ShannonSeverance ... and also for reducing clustering factor on particular indexed column(s) as well. — David Aldridge, Nov 21 '15 at 13:10
Related to http://stackoverflow.com/questions/1339991/insert-with-order-on-oracle/38486894 — vapcguy, Jul 20 '16 at 21:02

score 6 · Answer 1 · answered Aug 31 '17 at 14:01

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
)

score 5 · Answer 2 · answered Nov 21 '15 at 13:09

5

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.

answered Nov 21 '15 at 13:09

David Aldridge

51,479
8
68
96

1

but that is not lourdh's intention. The OP expects the rows returned in the order they were inserted when selecting them - which is a wrong assumption. And not even the `append` hint with an `order by` will guarantee that. – Nov 21 '15 at 13:51
@a_horse_with_no_name indeed, but I'm just focusing on the idea that an insert with order by is of no value. – David Aldridge Nov 21 '15 at 16:21
@a_horse_with_no_name ACTUALLY, it worked for me to both insert in the right order and show it when doing a `SELECT` on the table afterwards, so why can't it be "guaranteed" when dealing with static data, i.e. `ID`, `NAME` in a table of `Countries`, that inserting this data into another `New_Countries_Table` using the `/*+ append */` hint won't always work? Would like to know so I know when to use this hint and when not to... – vapcguy Jul 20 '16 at 20:15
@vapcguy: Because rows in a relational database are ***NOT*** "sorted". Without an order by the DBMS is free to return the rows in any order it likes. The `append` hint has nothing to do with "sorting" rows. – Jul 20 '16 at 20:18
@a_horse_with_no_name True, but that's why you append an `ORDER BY` clause on your `INSERT` statement (like in this answer, above). I get that, by itself, the hint won't do this. – vapcguy Jul 20 '16 at 20:24
@vapcguy Again: the order of inserting the rows it totally irrelevant for the order when the rows are retrieved. The **only** (really: the **only**) way to get a guaranteed sort order during retrieval is to use an `order by`. – Jul 20 '16 at 20:27
@a_horse_with_no_name On a normal `INSERT`, yes, I agree, it ignores the `ORDER BY`. But one where I used the hint and `ORDER BY` together, the hint actually enforced the `ORDER BY` and it worked for me. I was looking for a time when that combination might not actually work and a reason why it wouldn't. – vapcguy Jul 20 '16 at 20:32
@vapcguy: **Again** the order during insert is completely and utterly irrelevant for the order during retrieval. See e.g. here: http://stackoverflow.com/a/17059166/330315 or here: http://stackoverflow.com/q/899514/330315 – Jul 20 '16 at 20:33
@a_horse_with_no_name We're talking about 2 different things. *Again*, I agree that, on a normal `INSERT`, by itself, no `append` hint, it ignores the `ORDER BY`. But if you have the hint, it seems to enforce it. I was asking why there might be any case where it would not. – vapcguy Jul 20 '16 at 20:36
@vapcguy. you said it yourself: it _seems_ to "enforce" it. And what you see is pure coincidence. The ***only*** way to get a guaranteed order is to use `order by`. **There is *no* alternative**, none whatsoever. – Jul 20 '16 at 20:39
@a_horse_with_no_name I wasn't advocating to not use `ORDER BY`. But just as you said when you said "the order of inserting the rows [is] totally irrelevant for the order when the rows are retrieved", using `ORDER BY` by itself doesn't do anything to an `INSERT` statement - in fact, I got the wrong order. But when I combined the `ORDER BY` with the `/*+ append */` hint, it all worked and I got the right order. So this was coincidence? – vapcguy Jul 20 '16 at 20:44
@vapcguy: yes, that coincidence. – Jul 20 '16 at 20:49

score 2 · Answer 3 · answered Nov 21 '15 at 08:32

2

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.

answered Nov 21 '15 at 08:32

Lalit Kumar B

47,486
13
97
124

1

They're not logically in an order, but they are definitely in a physical order which has all sorts of implications. That's not to say that you don't need an order by on select, but it's important to acknowledge the physical ordering. – David Aldridge Nov 21 '15 at 13:12
But this is not what OP wants to know, or at least not in the scope of OP's question. OP wants the rows to be returned in the order rows were selected for the insert statement. – Lalit Kumar B Nov 21 '15 at 13:58
Quite so, but my comment addresses, "Sorting has nothing to do while inserting rows. and is completely pointless", and that table rows are not in an order. They may be useful in persuading people that they must use ORDER BY in select statements, with which I have no argument, but they are not true when you consider the physical implementation of the database. – David Aldridge Nov 21 '15 at 16:33

score 0 · Answer 4 · answered Nov 21 '15 at 08:25

0

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

answered Nov 21 '15 at 08:25

DougieHauser

460
6
14

Hi Hauser, I am using order by in my select sql. when i select the rows it is displaying in correct order. but when i use it in insert-select, it inserts in random order – lourdh Nov 21 '15 at 08:32
2

@lourdh: the `order by` you use in the `insert` statement is useless. You need to use an `order by` when you select from the table you inserted into – Nov 21 '15 at 08:36
@a_horse_with_no_name is exactly right. You can't control the order with which data is inserted into the table, but you don't need to - because you can control it whenever you query it using the ORDER BY clause in your SELECT statements. Do you have any special need for controlling order of inserted data? If so, tell us about it and we'll see if we have any good advise. :) – DougieHauser Nov 21 '15 at 08:50
1

@DougieHauser For my case, I had a table of Countries that had new records (i.e. South Sudan) put into it that were not alphabetical with the rest of the records above them. This makes for more (not much more, but still more) coding when binding the table to a dropdown on a page with `.DataBind()`, where you are not controlling the SQL you get, so it makes sense to start with the table, instead. For me, I was able to use `insert /*+ append */ into New_Countries select ID, NAME from Old_Countries order by NAME ASC` to much success. – vapcguy Jul 20 '16 at 20:27

Oracle Insert Select with order by

4 Answers4

Linked

Related