| Update a big table [message #23408] |
Thu, 05 December 2002 14:37  |
Reddy Peram
Messages: 52
Registered: December 2002
|
Member |
|
|
I need to update TableA by using TableB. Both tables have around 30 million records and updatable records could be around 100,000 daily.I need to run this process daily. I am using the following sql to do the update process which is taking around 18+ hours. I need to make this process as short as possible (3-4 hours).
UPDATE tableA MFC
SET MFC.COLUMN1 =
(
SELECT CFC.COLUMN1
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN2=
(
SELECT CFC.COLUMN2
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN3=
(
SELECT CFC.COLUMN3
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN4 =
(
SELECT CFC.COLUMN4
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN5 =
(
SELECT CFC.COLUMN5
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN6 =
(
SELECT CFC.COLUMN6
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN7 =
(
SELECT CFC.COLUMN7
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN8 =
(
SELECT CFC.COLUMN8
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN9 =
(
SELECT CFC.COLUMN9
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
),
MFC.COLUMN10=
(
SELECT CFC.COLUMN10
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
)
WHERE EXISTS
(
SELECT CFC.ACCOUNT_KEY,
CFC.STMNT_SEQ_NO
FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO
);
COMMIT;
|
|
|
|
| Re: Update a big table [message #23410 is a reply to message #23408] |
Thu, 05 December 2002 15:17  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Wow, you are having Oracle do way more work than needed. This should be:
update tableA MFC
set (column1, column2, ...) =
(select CFC.column1, CFC.column2, ...
from tableB CFC
where CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
and CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO)
where exists (select null
from tableB CFC
where CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
and CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO);
Obviously you want an index on tableB(account_key, stmnt_seq_no).
|
|
|
|
| Re: Update a big table (followup) [message #23411 is a reply to message #23408] |
Thu, 05 December 2002 15:55 |
Reddy Peram
Messages: 52
Registered: December 2002
|
Member |
|
|
Todd,
Thanks for your quick response. Almost 97% of table A does have a match in table(Keys). As you said, I do want to update rows that DO NOT match on the non-key columns while comparing on key-columns. Let me tell you exact process.
Both tables have 30 M records. At the end of the daily process both tables are exactly duplicates but in different databases. Table B gets updated and inserted through some other process (daily updates and insert count is around 100,000; out of which 20,000 updates, 80,000 inserts). I need to do the same process for Table A by using Table B. So first I am updating by using WHERE EXISTS, then Inserting by using WHERE NOT EXISTS. Please suggest me.
|
|
|
|
| Re: Update a big table [message #23516 is a reply to message #23408] |
Fri, 13 December 2002 19:18 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
As todd said, it is an absolute longer and unnecessary process of SQL. You are repeating same SELECT statement for each column update forcing ORACLE to SELECT the records again and again for same. Put them in one query and for one set of updated columns with WHERE EXISTS or a VIEW creation. It seems you are new to ORACLE.
UPDATE tableA MFC set (col1,col2,...col10) = (SELECT col1,col2,col3..col10 FROM tableB CFC
WHERE CFC.ACCOUNT_KEY = MFC.ACCOUNT_KEY
AND CFC.STMNT_SEQ_NO = MFC.STMNT_SEQ_NO)
Its that simple. I did not understand you want to update the unmatched records. What do you update with unmatched records?
And FYI, ORACLE does not take 3-4 hours long even the database has over a million records. Check it out.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
