1

i want to insert data in unique2 table from unique1 table in the same order it exists in unique1 table. 

insert /*+ append */ into unique2 
        select * from unique1 A where not exists (select 1 from match1 B where A.  promolog_key = B. promolog_key) order by rowid asc;

does the above query inserts the qualified data in the same order it exists in unique1?

thanks

subash
  • 69
  • 2
  • 5
  • 1
    What would it mean to you for data to be inserted in a particular order? Given that a heap-organized table is inherently unordered, the physical order of rows is generally pretty meaningless. The only exception I can think of would be if `unique2` was compressed and you were trying to get rows with repeating values in the same block. But your `order by rowid` strongly implies that this isn't the case here. – Justin Cave Feb 19 '13 at 16:06
  • "Order" has no meaning to how rows are stored in a table, and it is a moot effort as you impose order in your select statement to retrieve the rows. – WoMo Feb 19 '13 at 16:11
  • 1
    @JustinCave The order of the rows can have a large impact on index range scan performance, which can have a large impact on the optimizer. I suppose if one wanted to copy a table for testing purpose it would be best if the source table and its copy had the same ordering (of course it is not the *only* important factor =) – Vincent Malgrat Feb 19 '13 at 16:14
  • @VincentMalgrat - Sure, a heap-organized table can be ordered to make it more efficient to use any one index to do a range scan. But if that's the desire, it would generally make far more sense to use an index-organized table or a hash cluster rather than a heap-organized table. – Justin Cave Feb 19 '13 at 16:23
  • @JustinCave And they can only order them by their PK. I can imagine reasons where you might want data ordered in a certain way for heap tables, for example with a materialized view refreshed daily for reporting purpose. It might be interesting to order this MV a certain way (different from its PK) so that queries will use indexes more efficiently. – Vincent Malgrat Feb 19 '13 at 16:33
  • possible duplicate of [INSERT with ORDER on Oracle](http://stackoverflow.com/questions/1339991/insert-with-order-on-oracle) – Ben Feb 19 '13 at 16:39
  • @Ben: It's a different question, the OP here asks if he can influence the physical order of the rows with an `INSERT` **with** `ORDER BY` whereas the other question asks if a `SELECT` **without** an `ORDER BY ` will retrieve the rows in a predictable way. – Vincent Malgrat Feb 19 '13 at 16:44
  • Is it @vincent? How does _"Is there any way to control the order in which our INSERT statement adds records to the destination table?"_ differ from _"i want to insert data in unique2 table from unique1 table in the same order it exists in unique1 table"_? – Ben Feb 19 '13 at 16:46
  • @Ben I agree with Vincent that the intent of the questions are different -- that other question is really about performing an ordered insert with the intent of being able to avoid an order by on the select from the inserted table. – David Aldridge Feb 19 '13 at 17:02
  • @Ben They are very similar now that I re-read them. I think the other question was *interpreted* as "how can I insert data in a table so that it can be retrieved ordered without an `ORDER BY` clause?" Answer: not really possible. This question is slightly different since you can actually insert the rows sequentially with an `ORDER BY`. – Vincent Malgrat Feb 19 '13 at 17:04

2 Answers2

1

Yes, in general the physical row ordering will be the same.

Row ordering on insert relies on using direct path, and this is a fairly common technique in data warehouses or other systems that rely on it to get advantageous compression ratios and/or low clustering factors on particular indexes (which can be a strong driver to reduce bitmap index size).

Functionally however, ordering by rowid seems a bit doubtful. If the source table is already ordered in the desired manner then promoting a full table scan on origin1 would do the job. Otherwise I'd order on the column(s) that you want to improve clustering/compression through.

Although index-ordered tables or hash clusters would give a similar effect with regard to clustering factor, they come with restrictions that may not be helpful. In the case of IOT's it's often helpful with large inserts to order the rows as part of the select to get more efficient maintenance on the target table.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

Oracle lets you specify an ORDER BY clause in your INSERT statement and the documentation states:

  • With regard to the ORDER BY clause of the subquery in the DML_table_expression_clause, ordering is guaranteed only for the rows being inserted, and only within each extent of the table. Ordering of new rows with respect to existing rows is not guaranteed.

So the new rows will be inserted into your copy table in the same order as you specify in your ORDER BY clause. This doesn't affect old rows that are already in the table obviously.

The restriction on extent may not concern you because you are using the /*+ append */ hint and will therefore only insert above the watermark (sequentially if not parallel insert).

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • I think that the docs are only correct in respect of direct path inserts, and even then the reference to extents are not really correct. Parallel direct path inserts define new extents for each PQ slave, but serial direct path insert does not. I submitted a reader comment for the page. – David Aldridge Feb 19 '13 at 17:07
  • @DavidAldridge I'm intrigued by this restriction as well. It may apply to standard inserts where the inserted data will only be ordered within each extent inserted into. I think serial direct path inserts are safe (this has been my experience at least). – Vincent Malgrat Feb 19 '13 at 17:24
  • does it mean that if i use direct path inserts or direct path inserts with parallel the order would not be garunteed? – subash Feb 19 '13 at 17:38
  • Direct path inserts without parallel will be ordered. I'm not sure about parallel and not sure either about standard insert. – Vincent Malgrat Feb 20 '13 at 08:55
  • I'm pretty sure that standard inserts would not be ordered, as the blocks into which rows are inserted is intended to be to some degree distributed across available blocks below the HWM. Parallel direct path ... also not sure. No instance available to test it on, but it'd be simple to do. – David Aldridge Feb 20 '13 at 19:57