Home » RDBMS Server » Performance Tuning » Tuning update query - for more than 3 billion rows
Tuning update query - for more than 3 billion rows [message #659524] Fri, 20 January 2017 05:34 Go to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
Hi All,


We are running one update query and the table has more than 3 billion rows and it is taking more than 6 hrs to update the full table.
Now I want to tune the query, I have applied Index optimizer hint but when I run explain plan it is showing full table scan.

Can any one help me how to force an Index hint in update query ?
Also please tell me what are all the ways we have to improve the performance.



Thanks,
KRK
Re: Tuning update query - for more than 3 billion rows [message #659525 is a reply to message #659524] Fri, 20 January 2017 05:35 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If you're updating the full table it'll never want to use an index. Why would you want the additional overhead?

Consider not updating but recreating the table instead. It will be orders of magnitude faster.
Re: Tuning update query - for more than 3 billion rows [message #659526 is a reply to message #659524] Fri, 20 January 2017 05:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Also please tell me what are all the ways we have to improve the performance.
You could staert by reading these two books (both written by OraFAQ members) they are the best I have read. They take very different approaches. Kevin's requires understanding of the data and the query, Leonid's is more declarative:

http://www.amazon.com/Formal-SQL-Tuning-Oracle-Databases/dp/3662504162
http://www.amazon.com/Oracle-Performance-Tuning-Optimization-Cardinalities/dp/1501022695


Re: Tuning update query - for more than 3 billion rows [message #659527 is a reply to message #659525] Fri, 20 January 2017 05:45 Go to previous messageGo to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
Thank you very much for quick response

So you want me to create a separate table with the update query ?
we have some functions (data masking functions) in that update query and using for loop to update the rows.

and If I create a separate table I have to re-build all the dependencies like creating Indexes, primary keys ?
Re: Tuning update query - for more than 3 billion rows [message #659528 is a reply to message #659527] Fri, 20 January 2017 05:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You said "We are running one update query" but now you say "using for loop to update the rows". So which is it? A SQL statement that updates all the rows in one go, or a PL/SQL block that loops through them one by one?

Re: Tuning update query - for more than 3 billion rows [message #659531 is a reply to message #659528] Fri, 20 January 2017 06:10 Go to previous messageGo to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
a PL/SQL block which has a for loop inside and updates one by one.
Sorry.. my question was not clear.

Re: Tuning update query - for more than 3 billion rows [message #659532 is a reply to message #659531] Fri, 20 January 2017 06:17 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Ah. Well, running a statement 3 billion times that updates one row each time in just six hours is pretty good performance. You need to think about adjusting your code to do it in one huge update statement (you may need an undo tablespace the size of Jupiter for that).

Another approach would be to use dbms_redefinition. You can do the updates in the COL_MAPPING argument.
Re: Tuning update query - for more than 3 billion rows [message #659533 is a reply to message #659527] Fri, 20 January 2017 06:17 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
kurki wrote on Fri, 20 January 2017 11:45

and If I create a separate table I have to re-build all the dependencies like creating Indexes, primary keys ?

Yes. It's still going to be significantly faster.
Re: Tuning update query - for more than 3 billion rows [message #659535 is a reply to message #659524] Fri, 20 January 2017 07:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
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

Please post the procedure that is slow.
Re: Tuning update query - for more than 3 billion rows [message #659541 is a reply to message #659533] Fri, 20 January 2017 10:37 Go to previous messageGo to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
What if the table has any dependencies with other tables.
like this table is a parent for any other table
or
If this table is a child for any parent table?

and though we disable the constraints, we can not delete the table if there is any dependency

Please clarify
Re: Tuning update query - for more than 3 billion rows [message #659542 is a reply to message #659532] Fri, 20 January 2017 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John Watson wrote on Fri, 20 January 2017 13:17
Ah. Well, running a statement 3 billion times that updates one row each time in just six hours is pretty good performance. You need to think about adjusting your code to do it in one huge update statement (you may need an undo tablespace the size of Jupiter for that).

Another approach would be to use dbms_redefinition. You can do the updates in the COL_MAPPING argument.
Or use DBMS_PARALLEL_EXECUTE package (if OP's version knows it, of course).
Maybe disabling and rebuilding the indexes afterwards.

[Updated on: Fri, 20 January 2017 10:55]

Report message to a moderator

Re: Tuning update query - for more than 3 billion rows [message #659573 is a reply to message #659541] Mon, 23 January 2017 03:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
kurki wrote on Fri, 20 January 2017 16:37
What if the table has any dependencies with other tables.
like this table is a parent for any other table
or
If this table is a child for any parent table?

and though we disable the constraints, we can not delete the table if there is any dependency

Please clarify
The original question was about update not delete, so which are you doing?

For delete, deleting the child table is not an issue. If you're deleting the parent you can always set the fk to on delete cascade.
Re: Tuning update query - for more than 3 billion rows [message #659579 is a reply to message #659573] Mon, 23 January 2017 03:41 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I think it was in reference to my suggestion to recreate and drop the table. It is a definite hassle with RI, but it is still less painful in the main that trying to do that much updating.
Previous Topic: ora-01555 snapshot too old. Query executed 4000s
Next Topic: Identify index key entry by rowid
Goto Forum:
  


Current Time: Thu Mar 28 18:47:06 CDT 2024