Need to update nearly 3cr rows in Oracle table

35 pts.
Tags:
Oracle 8
Oracle 8 performance
Oracle performance
Oracle Table
HI , My database is oracle 8.0 vesion.i need to update the customer_id in in table with new_customer_id. for the smaller table my simple update is working fine .. when i fired the same update for the big tables ( 30000000 rows), its running for 10 hrs and failing with rollback segments. can any one tell me what is the best way to complete this update. they way i did for the samll table is SQL> SELECT * FROM CUST_OLD; CUST_ID NAME -------- -------- 111 AAA 222 BBB 333 CCC 444 DDD 111 AAA 222 BBB 555 EEE 7 rows selected. SQL> SELECT * FROM CUST_NEW; CUST_ID CUST_NEW -------- -------- 111 1111 222 2222 333 3333 444 4444 555 5555 666 6666 777 7777 888 8888 999 9999 SQL> UPDATE CUST_OLD A SET A.CUST_ID= 2 ( 3 SELECT 4 B.CUST_NEW_ID 5 FROM 6 CUST_NEW B 7 WHERE 8 A.CUST_ID=B.CUST_ID 9 AND B.CUST_NEW_ID IS NOT NULL) 10 WHERE EXISTS 11 (SELECT C.CUST_ID FROM CUST_NEW C WHERE A.CUST_ID=C.CUST_ID) ; 7 rows updated. SQL> SELECT * FROM CUST_NEW; CUST_ID CUST_NEW -------- -------- 111 1111 222 2222 333 3333 444 4444 555 5555 666 6666 777 7777 888 8888 999 9999 9 rows selected. SQL> SELECT * FROM CUST_OLD; CUST_ID NAME -------- -------- 1111 AAA 2222 BBB 3333 CCC 4444 DDD 1111 AAA 2222 BBB 5555 EEE 7 rows selected.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I have created a new table(rowid_bi_hdr) with two columns(rowid,bill_to_cust_id) from BASE table (PS_BI_HDR).
updated the NEW table with the help of new_bill_to_cust_id table.

now i want to update the PS_BI_HDR table with the help of NEW table using ROWID.

will it work?

update (
select /*+ full(a) parallel(a,4) full(b) parallel(b,4) */
a.rowid rowid_bi,a.bill_to_cust_id bill_cust_bi,
b.row_id row_id_uen,b.bill_to_cust_id bill_cust_uen
from
ps_bi_hdr a ,
rowid_bi_hdr b
where a.rowid=b.row_id)

set
bill_cust_bi=bill_cust_uen;

============================================

Does this have something to do with your original question ? If yes, please explain it a little more, because the relation is not obvious, but if not, I would suggest to remove this from here, and create a new Question.

As for your first question, this is the approach we have used in similar situations:

- Create a new table from a query from the old table(s), which gets the data as you want it to be after the update.
- index, grant, add constraints, etc… on the new table
- drop the old table
- rename the new table

Something like this:

<pre>create table TMP_CUST as
select a.field1, a.field2, … a.fieldn, b.cust_id
from cust_old a, cust_new b
where a.cust_id = b.cust_id
and b.cust_new_id is not null
UNION
select a.*
from cust_old a, cust_new b
where a.cust_id = b.cust_id (+)
and b.cust_id is null;</pre>
or
<pre>create table TMP_CUST as
select a.field1, a.field2, … a.fieldn, b.cust_id
from cust_old a, cust_new b
where a.cust_id = b.cust_id
and b.cust_new_id is not null
UNION
select *
from cust_old a
where not exists (select 1 from cust_new where cust_id = a.cust_id);</pre>

Then, grant, add constraints, index, etc… on TMP_CUST;
Then: drop table CUST_OLD;
then: rename TMP_CUST to CUST_OLD;

Another way to do it could be creating an empty table, and inserting the rows from the query

<pre>create table TMP_CUST as select * from CUST_OLD where rownum < 1;

insert /*+ append */ into TMP_CUST
select a.field1, a.field2, … a.fieldn, b.cust_id
from cust_old a, cust_new b
where a.cust_id = b.cust_id
and b.cust_new_id is not null
UNION
select a.*
from cust_old a, cust_new b
where a.cust_id = b.cust_id (+)
and b.cust_id is null;</pre>

I think this could work for you.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following