Home » SQL & PL/SQL » SQL & PL/SQL » Update Vs Delete&insertion (Oracle 11)
Update Vs Delete&insertion [message #658381] Mon, 12 December 2016 06:31 Go to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Hi

If we have very huge data which one is efficient way to update on record based on certain business conditions.
So want to check instead of update if we go for delete and insert will be it good ?

Thanks
SaiPradyumn
Re: Update Vs Delete&insertion [message #658382 is a reply to message #658381] Mon, 12 December 2016 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No it will not.

Re: Update Vs Delete&insertion [message #658383 is a reply to message #658382] Mon, 12 December 2016 06:40 Go to previous messageGo to next message
Bill B
Messages: 1799
Registered: December 2004
Senior Member
the problem is that your database has to backup the deleted data until the changes are deleted. the work to backup and alter a single column is MUCH less then backing up an entire row and then inserting a new one.
Re: Update Vs Delete&insertion [message #658386 is a reply to message #658383] Mon, 12 December 2016 07:17 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks for your valuable information .

If I am updating table with huge data, is it better to check the count of the record before updating.
My intention is if the updated records count is equals to ZERO, don't go for DML operation by retrieving the records count from the table .

Will this improve the performance?

Re: Update Vs Delete&insertion [message #658388 is a reply to message #658386] Mon, 12 December 2016 07:21 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Mon, 12 December 2016 05:17

Thanks for your valuable information .

If I am updating table with huge data, is it better to check the count of the record before updating.
My intention is if the updated records count is equals to ZERO, don't go for DML operation by retrieving the records count from the table .

Will this improve the performance?

what prevents you from doing benchmark testing to see for yourself which is faster?
what is the probability that ZERO rows need to be updated?

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

Re: Update Vs Delete&insertion [message #658389 is a reply to message #658388] Mon, 12 December 2016 07:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
It is not just about which is faster. It is logically different events. For example, replacing UPDATE with DELETE + INSERT might fail with "integrity constraint violated - child record found" if row we are deleting is parent row to some child table. We are also running DELETE and INSERT triggers (if there are any) instead of UPDATE triggers (if there are any). And even if there are no triggers now, we might add them later without realizing some piece of code switched UPDATE with DELETE + INSERT. Anyway, bad idea including performance.

SY.
Re: Update Vs Delete&insertion [message #658390 is a reply to message #658389] Mon, 12 December 2016 07:38 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Hi BlackSwan ,

I ma doing minimum testing with my own less data. At that pint of time there is no difference. As the don't have the access for production environment which contains very huge data I am unable to conclude the approach.
Re: Update Vs Delete&insertion [message #658391 is a reply to message #658390] Mon, 12 December 2016 07:40 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
It appears you suffer from Compulsive Tuning Disorder.
Re: Update Vs Delete&insertion [message #658393 is a reply to message #658386] Mon, 12 December 2016 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

saipradyumn wrote on Mon, 12 December 2016 14:17

Thanks for your valuable information .

If I am updating table with huge data, is it better to check the count of the record before updating.
My intention is if the updated records count is equals to ZERO, don't go for DML operation by retrieving the records count from the table .

Will this improve the performance?
There will be no difference between counting the number of rows which will return 0 and updating 0 rows, the work is the same one, worse is counting as there is slight (but negligible) more work.
Counting and updating when there is some rows is worst because:
1/ You have to select twice the same rows so double the work
2/ You are not sure that you will have the same count of rows as someone can have updated some of them between the 2 statements.

[Updated on: Thu, 15 December 2016 01:04]

Report message to a moderator

Re: Update Vs Delete&insertion [message #658456 is a reply to message #658393] Thu, 15 December 2016 00:47 Go to previous message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Mike for your valuable information .
Previous Topic: Bulk bind a single attribute of a collection
Next Topic: Concatenating an array output into one single string
Goto Forum:
  


Current Time: Mon Oct 15 19:17:54 CDT 2018