Home » RDBMS Server » Performance Tuning » regarding update on million of records (orcale 9i)
regarding update on million of records [message #312574] Wed, 09 April 2008 05:46 Go to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

what is the optimized way to update 10 millions of records on a table consisting of 100 millions of records????
Re: regarding update on million of records [message #312577 is a reply to message #312574] Wed, 09 April 2008 05:51 Go to previous messageGo to next message
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 #312619 is a reply to message #312577] Wed, 09 April 2008 07:59 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
See if this helps:
http://www.orafaq.com/tuningguide/high%20volume%20dml.html

Ross Leishman
Re: regarding update on million of records [message #312881 is a reply to message #312577] Thu, 10 April 2008 00:52 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

I know better what is the purpose of the forum .. my question is how we can update records in minimun time. I better aware of performance tuning related activities........
Re: regarding update on million of records [message #312886 is a reply to message #312881] Thu, 10 April 2008 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Buy faster disks and more powerful cpus and decrease concurrent workload.

Regards
Michel
Re: regarding update on million of records [message #312975 is a reply to message #312886] Thu, 10 April 2008 04:32 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

Hi,
actuaaly my query is

UPDATE UPM_PARTPRICELINK A SET FEEDNAME = INFEEDNAME WHERE EXISTS
(SELECT 1 FROM PARTPRICELINK B WHERE B.PARTPRICELINKID = A.PARTPRICELINKID);


UPM_PARTPRICELINK table has 20 crore records....

PARTPRICELINK table has 3 crore records...

updating is on basis of PARTPRICELINKID column which is common to both tables & INFEEDNAME is a varaible which we fecthing inside procedure as parameter...

i had already made indexes on columns PARTPRICELINKID which is unique....


query takes time is 11 mins approx for updation

but i want the result to be within 2-3 mins....
Re: regarding update on million of records [message #312997 is a reply to message #312975] Thu, 10 April 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is this Indian or Iranian crore?

Regards
Michel
Re: regarding update on million of records [message #313002 is a reply to message #312997] Thu, 10 April 2008 05:47 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

indian crores...

20,0000000
Re: regarding update on million of records [message #313093 is a reply to message #312975] Thu, 10 April 2008 12:10 Go to previous messageGo to next message
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 #313169 is a reply to message #313093] Fri, 11 April 2008 00:06 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

excatly, we need to update the table on weekly basis..........
Re: regarding update on million of records [message #313172 is a reply to message #313169] Fri, 11 April 2008 00:08 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

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.

Tell me about exchage partioning... is it some kind of partioning on tbales.. is i am correct
Re: regarding update on million of records [message #313222 is a reply to message #313172] Fri, 11 April 2008 02:04 Go to previous message
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

Previous Topic: database buffer cache
Next Topic: full tablescan though indexes exist
Goto Forum:
  


Current Time: Fri Dec 13 00:42:47 CST 2024