OraFAQ Forum: SQL & PL/SQL » Update Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Update Statement (Oracle 11g)

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

Update Statement [message #607172]

Mon, 03 February 2014 07:35

srinivas.k2005
Messages: 404
Registered: August 2006

Senior Member

Hi,

The table "RIT" has around 300 million records.
I need to add the new column to "RIT" table and update the column with sequence.
I planned to use below approach. But since there are millions of records, any suggestions on how to update??

TABLE "RIT" :

a NUMBER

b Varchar2(20)

c DATE

Adding new column,

ALTER TABLE RIT
ADD RIT_ID_SEQ NUMBER
/

CREATE SEQUENCE RIT_IDENTIFIER_SEQ
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1001
INCREMENT BY 1
CACHE 20000
/

UPDATE /*+ PARALLEL (rt,16) */ RIT rt
SET rt.RIT_ID_SEQ=RIT_IDENTIFIER_SEQ.nextval;

COMMIT;

Regards,
SRK

Report message to a moderator

Re: Update Statement [message #607173 is a reply to message #607172]

Mon, 03 February 2014 07:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what happens to new rows after COMMIT?

Report message to a moderator

Re: Update Statement [message #607175 is a reply to message #607172]

Mon, 03 February 2014 07:54

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

Senior Member

Perhaps this

UPDATE /*+ PARALLEL (rt,16) */ RIT rt
SET rt.RIT_ID_SEQ=rownum;

might perform better?
Why parallel 16?

Report message to a moderator

Re: Update Statement [message #607176 is a reply to message #607173]

Mon, 03 February 2014 08:00

srinivas.k2005
Messages: 404
Registered: August 2006

Senior Member

I have given the update statement. It is still running from past 4 hours now.
Any suggestions OR other Methods ??

Report message to a moderator

Re: Update Statement [message #607177 is a reply to message #607176]

Mon, 03 February 2014 08:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

is UPDATE session doing work or waiting now?

it could be waiting on different session to COMMIT so it can process the same row.

Report message to a moderator

Re: Update Statement [message #607180 is a reply to message #607177]

Mon, 03 February 2014 08:36

srinivas.k2005
Messages: 404
Registered: August 2006

Senior Member

I checked the below query:

select * from v$session where OSUSER=??

v$session.STATE='WAITING'

Regards,
SRK

Report message to a moderator

Re: Update Statement [message #607182 is a reply to message #607180]

Mon, 03 February 2014 08:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>v$session.STATE='WAITING'
so now you know why it is taking so long.
UPDATE is waiting to gain access to some locked row

We can not change what occurs inside your DB.

Report message to a moderator

Re: Update Statement [message #607186 is a reply to message #607182]

Mon, 03 February 2014 09:10

srinivas.k2005
Messages: 404
Registered: August 2006

Senior Member

Thanks for your input.

But I have created new table and inserted 300 million records into it from other table.

So nobody will be accessing the new table.

Regards,
SRK

Report message to a moderator

Re: Update Statement [message #607187 is a reply to message #607176]

Mon, 03 February 2014 09:13

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

Senior Member

srinivas.k2005 wrote on Mon, 03 February 2014 14:00

I have given the update statement. It is still running from past 4 hours now.
Any suggestions OR other Methods ??

You have started the job already? So why did you ask for advice on how to do it??
If you want to track it's progress, query v$session_longops. And to see why the session is waiting:
select event,seconds_in_wait from v$session where...

--update: you could also query v$transaction, to see how much undo is being generated.

[Updated on: Mon, 03 February 2014 09:13]

Report message to a moderator

Re: Update Statement [message #607188 is a reply to message #607186]

Mon, 03 February 2014 09:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>But I have created new table and inserted 300 million records into it from other table.
was COMMIT issued?

>v$session.STATE='WAITING'
Oracle is too dumb to lie about such details

Report message to a moderator

Re: Update Statement [message #607194 is a reply to message #607172]

Mon, 03 February 2014 12:04

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Using sequence in update will slow you down. Use ROWNUM instead:

UPDATE RIT rt
SET rt.RIT_ID_SEQ = 1000 + ROWNUM
/
CREATE SEQUENCE RIT_IDENTIFIER_SEQ
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1001 + table RIT row count
INCREMENT BY 1
CACHE 20000
/

SY.

Report message to a moderator

Re: Update Statement [message #607195 is a reply to message #607194]

Mon, 03 February 2014 12:07

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

And don't forget to disable UPDATE triggers if there are any.

SY.

Report message to a moderator

Previous Topic:	How to send mail from an Oracle database?
Next Topic:	create or replace context abc Using package_name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 19:48:15 CDT 2024