| duplicate pk_key records to insert with different column values [message #55522] |
Tue, 28 January 2003 21:19  |
oracle
Messages: 9
Registered: May 2002
|
Junior Member |
|
|
hi guys,
i have two problems, and they are not as simple as look like from the subject name. the first one is as follows:
we have a huge table with 20 columns and more than 50000 rows which holds some old data. Another table with 12 columns and about 17000 rows, holding new data.
But most of these data belong to the same items as the older ones , but they have some new , updated data in some of their columns. Now the task is:
i have to gather all these data (two tables) so that the data in the newer table will replace the older ones if already exists, if not then simply inserted.
both of them has a unique field (which supposed to be unique but actually not) called totid. now i want to update a certain column with newer ones , if the newer column is not null.
here the difficult part is, about 16000 of these rows are same as the older ones,so i need to make a large number of update process. As you might have experienced before update takes a long time, especially if there are more thatn 50000 rows.
i try to solve the problem by writing a java program which selects data from newer table, checks if null or not, if there is some data updates the corresponding row in the older table. It works, but it is too slow, i calculated , it needs 12 hours to update just one column, which is not acceptable. I can accept a few hours for a column updating process.i also tried batch updateing but didn't make much difference. Then i try to seek the solution in PL/sql. i used cursors to make recursive update process, but it stil takes too much time. Worse, once i start my procedure it doesn't show any sign of processing, i just wait hours to see the result. I embedded some dbms_output_put_line statemnets in my procedure so that i can see what is going on during the execition. My procedure succeded to compile, but at runtime still there are no outputs.
can some one help me with this.I need the experienced guys experiences.Any idea , suggestions are welcome.We are working on a project , and the time is short, we need to finish this task as soon as possible. I need your help.
my second problem is even more difficult.
i couldn't find a solution yet.
these 50000 rows have some items with the same totid , which was supposed to be primary key and unique. Since it is not, i can't define it as P.k, i can't define index to speed my process. any idea how to delete those duplicate rows?
i used select distinct totid,.... but it didn't work.Because those items with same totid have different data in other columns, so select distinct can't distinguish distinct rows.
thanks for your taking time.
have a nice day :)
|
|
|
|
| Re: duplicate pk_key records to insert with different column values [message #55526 is a reply to message #55522] |
Wed, 29 January 2003 01:55  |
psmyth
Messages: 81
Registered: October 2002
|
Member |
|
|
first problem - get a bigger 'rubber band' for your server - a crude solution, but its simple and it will improve your processing times. But I'm sure someone will come up with a more elegant solution soon ;-)
second problem - i read it as how to uniquely identify rows in a table with duplicates... use 'rowid'.
ie
select distinct rowid, totid from table order by totid, rowid;
each row will have a distinct rowid ireespective of whether the totid is unique or not. Now all you have to do is write a sql statement that deletes the rows you don't want... one way is for every row that has a duplicate, delete it if it has the highest rowid of the duplicates.
There's probably faster ways, but thats a start.
Don't forget to copy the table before you start deleting ;-)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
