Hi,
Is the following a contract Oracle will uphold? (I have found plenty of docs related to top 'n' queries and order by statement usage, but does Oracle specifically up hold the in line view 'order by' statement if the query engine detects rownum keyword usage.
My scenario (please note this has already been done and we have a sev1 issue I'm trying to resolve):
I have a table with a surrogate key but thanks to some multi threading enhancements to our application the surrogate key no longer gives us a guaranteed chronological order. We have a date column (NB not timestamp) and I am restricted to not introducing timestamps to the system at this time, so, given this, the date column is not granular enough to get a chronological order. The system is legacy, the id column is not based on a sequence (I Know!!!) and changing it to a sequence requires too many invasive changes in our code.
So I can add a new column with a sequence associated to it via a trigger - all good so far.
Now I need to populate the sequence on VERY large datasets so the following SQL is employed +(note: the old code base does not have the optimaisations so I can use the existing surrogate key to get initial sequencing information.)+
-----
create table_new (id, a,b, new_seq_col) no_logging; -- this is an empty version of the exisitng table with the new sequence column
insert /*+ append */ into table_new
(id, b, c, new_seq_col)
select id,b, c, seqeunce.nextval
from (select id,b,c from old_table order by id)
drop old_table; (after some checks)
rename table_new to old_table; -- and enable logging...
-----
My main question is:
Will Oracle uphold the 'order by' contract for the insert? I realise that there are alternative/better ways (e.g. forall processing) to do this, but this is what has been done, and our client is seeing new sequence values out of sync.
Is it possible that the append hint may cause the ordering to not be guaranteed? As I understand the append hint is just about losing the ability to undo the operation.
Edited by: user1123092 on 03-Aug-2010 06:40