[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Parallel update or serial update or bulk update.?

Posted on 2012-09-17
8
Medium Priority
?
510 Views
Last Modified: 2012-09-17
I want to update a table of 5 million rows.(Want to update all rows)

I want to update a newly added column with old one old column.

Which method I should choose,

I tried using simple update statement like the following

update contrs
set uno_temp = uno;


But this takes huge time, Please suggest me a best approach.
0
Comment
Question by:sakthikumar
  • 4
  • 2
  • 2
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38406427
the simple update should be the fastest way.

if it's taking a very long time, you're probably waiting on other sessions that have one or more rows locked.

Another thing to consider is you might be waiting on lots of row migration.

Check your wait states what is it your update is waiting on?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38406440
if the table is partitioned you might get some benefit from using the parallel hint


also, I'm assuming here and above that the column in question is a simple number, date or varchar2 (something small) not  CLOB or BLOB
0
 

Author Comment

by:sakthikumar
ID: 38406486
yes it is not a blob or clob. And also no row locks from other sessions.

how to check wait states, any way without running AWR?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1332 total points
ID: 38406521
look in the event column of v$session
0
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 668 total points
ID: 38406720
Is it a plain table or does it have triggers, sequences etc? That would slow things down.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1332 total points
ID: 38406729
A sequence won't have any effect unless it's being accessed by a trigger or fga/fgac  policy.

And, of course, if you have any of those, all bets are off as to how long it will take.
0
 

Author Comment

by:sakthikumar
ID: 38408220
I forgot to check the triggers for the table.

Disabling the trigger does the trick, Statement executed in 1 minute.

Thanks everyone.
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 38408228
You're welcome :)
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question