Home » SQL & PL/SQL » SQL & PL/SQL » merging the data
merging the data [message #218775] Sat, 10 February 2007 02:07 Go to next message
Messages: 3
Registered: February 2007
Location: DUBAI
Junior Member
i want to merge a more than 4 billion records into another table having 20 to 30 milion records. if records from the first table exist in the second then a column value should be added together else it should be inserted as a new. i have tried a merge statement but it is not reliable because sometimes it works fine and sometimes it gives error "unstable set of rows". other thing i have to keep in consideration is to make this whole data available to users in least possible time. i was thinking of using some other logic other than using merge ststement than the that will be very time cosuming. both the tables are partitioned and indexes are on the all the coloumns except one measure column.

has anybody some suggestions which can help me go through.
Re: merging the data [message #218823 is a reply to message #218775] Sat, 10 February 2007 18:57 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why don't you set up the billion row table so that it look exaclty like the other table, and then merge the million rows into that. When its done, drop the million row table and rename the bigger one.

This will still take a while if most of the million rows are going to be found in the bigger table. In this case it would be better to build the final table using CREATE TABLE AS SELECT ... and then rename it when it is built. More on bulk DML here.

Ross Leishman
Previous Topic: Accessing Data From Oracle Reports
Next Topic: Find the maximum value.
Goto Forum:

Current Time: Wed Jul 26 06:01:20 CDT 2017

Total time taken to generate the page: 0.11777 seconds