Tuning update query - for more than 3 billion rows [message #659524] |
Fri, 20 January 2017 05:34 |
|
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 #659532 is a reply to message #659531] |
Fri, 20 January 2017 06:17 |
John Watson
Messages: 8951 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 #659542 is a reply to message #659532] |
Fri, 20 January 2017 10:40 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
John Watson wrote on Fri, 20 January 2017 13:17Ah. 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 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
kurki wrote on Fri, 20 January 2017 16:37What 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.
|
|
|
|