|
Re: regarding update on million of records [message #312577 is a reply to message #312574] |
Wed, 09 April 2008 05:51 |
|
Michel Cadot
Messages: 68722 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Do you see what is the purpose of this forum?
It is:
Quote: | Post your feedback and suggestions here. All suggestions, feedback, comments, ideas, etc are more than welcome.
|
Is your question a feedbacb, a suggestion, a comment about the site?
Post in Performance forum for performance question. Isn't it logical.
Also read the guidelines and concerning performances read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Oracle SQL Tuning Guide
Regards
Michel
[Edit: I moved the topic to the appropriate forum]
[Updated on: Wed, 09 April 2008 05:52] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: regarding update on million of records [message #313093 is a reply to message #312975] |
Thu, 10 April 2008 12:10 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: |
UPDATE UPM_PARTPRICELINK A SET FEEDNAME = INFEEDNAME WHERE EXISTS
(SELECT 1 FROM PARTPRICELINK B WHERE B.PARTPRICELINKID = A.PARTPRICELINKID);
|
Am I right in saying you want to update all the in upm_partpricelink with the infeedname where you could find a match with partpricelink. I believe you will be scanning it only once in your procedure. It's worthwhile looking into the post by @rleishman. We do more or less a similar sort of update/insert into a 86M row table every month. This process virtually updates all the rows in the table every month. We have adopted the way of creating a temporary table (direct path insert) and doing a partition exchange. If we would have gone for update it will be running forever (real long time).
Just a thought.
regards
Raj
|
|
|
|
|
Re: regarding update on million of records [message #313222 is a reply to message #313172] |
Fri, 11 April 2008 02:04 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: |
You mean that i should create a temp table and insert the records into it. After that i should drop the old table and rename the temp table.
|
Yes, I would like create a Temp table permanently on your schema and the structure is a replica of your source table. Code snippet for exchange partition way will be something like this. This is assuming temp table is un-partitioned table and your source table is partitioned.
truncate table temp_table;
insert into temp_table
<select query>
rebuild the index in temp_table
Exchange partition query along with index option
How to do exchange partition check this link.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#CJAHHIBI
Hope that helps.
Regards
Raj
|
|
|