Home » RDBMS Server » Performance Tuning » Performance issue to Update data (Oracle 11g)
Performance issue to Update data [message #664556] Sat, 22 July 2017 09:05 Go to next message
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?




Re: Performance issue to Update data [message #664557 is a reply to message #664556] Sat, 22 July 2017 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25751
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?


Re: Performance issue to Update data [message #664558 is a reply to message #664556] Sat, 22 July 2017 09:52 Go to previous messageGo to next message
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




Re: Performance issue to Update data [message #664559 is a reply to message #664558] Sat, 22 July 2017 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25751
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
Re: Performance issue to Update data [message #664560 is a reply to message #664559] Sat, 22 July 2017 10:03 Go to previous messageGo to next message
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
Re: Performance issue to Update data [message #664564 is a reply to message #664560] Sat, 22 July 2017 10:33 Go to previous messageGo to next message
BlackSwan
Messages: 25751
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
Re: Performance issue to Update data [message #664575 is a reply to message #664560] Sun, 23 July 2017 05:10 Go to previous messageGo to next message
John Watson
Messages: 7188
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.
Re: Performance issue to Update data [message #664604 is a reply to message #664575] Mon, 24 July 2017 08:21 Go to previous message
Bill B
Messages: 1701
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;
Previous Topic: insert query
Next Topic: Join on index and adding default_where
Goto Forum:
  


Current Time: Sun Dec 17 18:47:46 CST 2017

Total time taken to generate the page: 0.07234 seconds