OraFAQ Forum: Performance Tuning » Performance issue to Update data

Home » RDBMS Server » Performance Tuning » Performance issue to Update data (Oracle 11g)

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

Performance issue to Update data [message #664556]

Sat, 22 July 2017 09:05

newuser
Messages: 3
Registered: December 2009
Location: pune

Junior Member

I have a one table which is storing last 15yrs of data around 300 millions of rows

Create table tbl
(col_id numeric,
col_name varchar2(100),
column_data1 numeric,
column_date2 numeric,
column_dt date,
-
-
-);

There is no indexes on table but have the range partition on column_dt.

I want to update the data a on specific condition (combination of column_date1 and column_date2 or say column_data1 = 111 and column_date2 = 222)

note - tables have remaining combination of data.

I have tried create table

tbl_bk as select * from bk;

but facing performance issue.

please suggest what is fastest and efficient way to update such large table?

Report message to a moderator

Re: Performance issue to Update data [message #664557 is a reply to message #664556]

Sat, 22 July 2017 09:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

I am confused about what needs to occur.

>I want to update the data a on specific condition
What does below have to do with above?
>create table tbl_bk as select * from bk;

Out of the 300 million rows, how many rows are to be UPDATED?

Report message to a moderator

Re: Performance issue to Update data [message #664558 is a reply to message #664556]

Sat, 22 July 2017 09:52

newuser
Messages: 3
Registered: December 2009
Location: pune

Junior Member

1. Out of the 300 million rows, how many rows are to be UPDATED? ---- around 100 millions

2. want to perform below update
update tbl
set column_data1 = 555
where column_data1 = 111
and column_data2 = 222;

please suggest fastest and easiest way

Report message to a moderator

Re: Performance issue to Update data [message #664559 is a reply to message #664558]

Sat, 22 July 2017 09:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

UPDATE might be faster with index on COLUMN_DATA1 & COLUMN_DATA2; but you can only be sure by actual testing & measuring results

Report message to a moderator

Re: Performance issue to Update data [message #664560 is a reply to message #664559]

Sat, 22 July 2017 10:03

newuser
Messages: 3
Registered: December 2009
Location: pune

Junior Member

index creation itself taking too much time.

can I use bulk collect to update the data. please give me sample of code for reference

Report message to a moderator

Re: Performance issue to Update data [message #664564 is a reply to message #664560]

Sat, 22 July 2017 10:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

bulk collect will be slower than plain SQL

Report message to a moderator

Re: Performance issue to Update data [message #664575 is a reply to message #664560]

Sun, 23 July 2017 05:10

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

Senior Member

You could (if you have Enterprise Edition) enable parallel DML and try a parallel update. Or in Standard Edition, use dbms_parallel_execute.

Report message to a moderator

Re: Performance issue to Update data [message #664604 is a reply to message #664575]

Mon, 24 July 2017 08:21

Bill B
Messages: 1971
Registered: December 2004

Senior Member

What version of the database do you have. Version 11 and above has a new ONLINE clause for index creation that will allow all your users to continue to use the table (insert, update, delete) until the index creation is completed. Just start it and walk away until it's done.

CREATE INDEX tbl_i1 ON tbl (column_data1,column_data2) ONLINE;

Report message to a moderator

Previous Topic:	insert query
Next Topic:	Join on index and adding default_where

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 21:08:36 CST 2025