OraFAQ Forum: SQL & PL/SQL » Data migration

Home » SQL & PL/SQL » SQL & PL/SQL » Data migration

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data migration [message #626779]

Sun, 02 November 2014 19:26

NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide

Member

Hi
I am working on oracle 11g. I am writing a procedure to update two tables. Each of these table has around 1000000 rows. This is first time I am writing a procedure to update almost every row of the table. What are the precautions I need to take? How can I make this procedure more efficient?

Cheers,
Leena

Report message to a moderator

Re: Data migration [message #626781 is a reply to message #626779]

Sun, 02 November 2014 19:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

NEVER do in PL/SQL that which can be done in plain SQL.
PL/SQL is simply wrapper code which never directly accesses any data within the database.
Only plain SQL can access data within the database.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Data migration [message #626805 is a reply to message #626779]

Mon, 03 November 2014 02:39

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I am working on oracle 11g.

You should then change your account name. Smile

Report message to a moderator

Re: Data migration [message #626856 is a reply to message #626805]

Tue, 04 November 2014 03:08

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

The stock answer (which I've almost never seen work in production because of various reasons) is to not bother but create a new table with the updated data and rename the old and new. Far and away the fastest.

If that's not an option, as BlackSwan says don't bother with PL/SQL, do it in straight sql.

Other things to consider which only you know if you can do or not are:
>Getting rid of triggers
>Dropping/disabling indexes
>Parallel dml

Report message to a moderator

Re: Data migration [message #626859 is a reply to message #626779]

Tue, 04 November 2014 03:32

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

NewToOracle10g wrote on Mon, 03 November 2014 01:26

Hi
I am working on oracle 11g. I am writing a procedure to update two tables. Each of these table has around 1000000 rows. This is first time I am writing a procedure to update almost every row of the table. What are the precautions I need to take? How can I make this procedure more efficient?

Cheers,
Leena

Updating a million rows should take only a few minutes, no matter how you do it. I cannot think of any particular precautions you need take. If anything does go wrong, you can rely on the database to rollback any changes. I would monitor the wait events during the update, that is all.

Report message to a moderator

Re: Data migration [message #626865 is a reply to message #626779]

Tue, 04 November 2014 04:03

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

Indexes can be really helpful to make it faster. Check if you have indexes on columns which are used in filter.

Report message to a moderator

Re: Data migration [message #626867 is a reply to message #626865]

Tue, 04 November 2014 04:05

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

OP wants to update almost all of the rows, why would you think indexes are a good idea in that case?

Report message to a moderator

Re: Data migration [message #626868 is a reply to message #626867]

Tue, 04 November 2014 04:10

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

I think we are not sure if OP is updating all rows without any logic. Is he using a reference table to arrive on some value to be updated in original table. That is why i mentioned if any column is used in filter.

Report message to a moderator

Re: Data migration [message #626870 is a reply to message #626868]

Tue, 04 November 2014 04:23

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

OP says almost all rows, so obviously there is logic, but if the number of rows is a significant percentage of the total you do not want it to use an index.

Report message to a moderator

Re: Data migration [message #626897 is a reply to message #626870]

Tue, 04 November 2014 20:37

NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide

Member

Hi
Below is the table.

SKRTCND_CONTRACT_ID          NOT NULL VARCHAR2(10 CHAR) 
SKRTCND_TERM_CODE            NOT NULL VARCHAR2(6 CHAR)  
SKRTCND_PIDM                 NOT NULL NUMBER(8)         
SKRTCND_TRAIN_ORG_PIDM                NUMBER(8)         
SKRTCND_EMP_ORG_PIDM                  NUMBER(8)         
SKRTCND_SEQ_NO               NOT NULL NUMBER(5)         
SKRTCND_CONTRACT_TYPE_CODE            VARCHAR2(4 CHAR)  
SKRTCND_PROGRAM                       VARCHAR2(12 CHAR) 
SKRTCND_START_DATE           NOT NULL DATE              
SKRTCND_END_DATE             NOT NULL DATE              
SKRTCND_SCHOOL_BASED_IND              VARCHAR2(1 CHAR)  
SKRTCND_CREDIT_IND                    VARCHAR2(1 CHAR)  
SKRTCND_EMPLOYMENT_TYPE_CODE          VARCHAR2(2 CHAR)  
SKRTCND_FULL_PART_IND                 VARCHAR2(1 CHAR)  
SKRTCND_NOMINAL_DURATION              NUMBER(2)         
SKRTCND_EXISTING_WORKER_IND           VARCHAR2(1 CHAR)  
SKRTCND_ANZSIC_CODE                   VARCHAR2(4 CHAR)  
SKRTCND_ASCO_CODE                     VARCHAR2(7 CHAR)  
SKRTCND_ACTIVITY_DATE        NOT NULL DATE              
SKRTCND_USER_ID              NOT NULL VARCHAR2(30 CHAR) 
SKRTCND_DATA_ORIGIN                   VARCHAR2(30 CHAR) 
[b]SKRTCND_EXTERNAL_ID [/b]                  VARCHAR2(10 CHAR) 
SKRTCND_STA                           VARCHAR2(9 CHAR)  
SKRTCND_SURROGATE_ID                  NUMBER(19)        
SKRTCND_VERSION                       NUMBER(19)        
SKRTCND_VPDI_CODE                     VARCHAR2(6 CHAR)  
SKRTCND_STSP_KEY_SEQUENCE             NUMBER(2)         
SKRTCND_TERMINATE_DATE                DATE              
SKRTCND_STATE_AUTH_DATE

In this SKRTCND_EXTERNAL_ID used to be blank until now. Due to some new development it needs to be populated depending on values in another table SORLCUR. So for each row I need to fetch a value from SORLCUR and update SKRTCND_EXTERNAL_ID.

Thanks for all replies and point noted Michel.

Cheers,
Leena

*BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/

[Updated on: Tue, 04 November 2014 20:42] by Moderator

Report message to a moderator

Re: Data migration [message #626898 is a reply to message #626897]

Tue, 04 November 2014 20:39

NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide

Member

Oops I did not post with smartie smilies. It should read ( 8 )

Report message to a moderator

Re: Data migration [message #626900 is a reply to message #626898]

Wed, 05 November 2014 00:46

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is because you didn't read the links BlackSwan gave you.

A single UPDATE statement will do what you want to do:
UPDATE mytable SET SKRTCND_EXTERNAL_ID = (SELECT ... FROM SORLCUR WHERE ...)
WHERE ...

Report message to a moderator

Previous Topic:	Change the Data type of non empty column
Next Topic:	how to handle zero divide error in formula calculation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:47:30 CDT 2024