OraFAQ Forum: SQL & PL/SQL » Update Vs Delete&insertion

Home » SQL & PL/SQL » SQL & PL/SQL » Update Vs Delete&insertion (Oracle 11)

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

Update Vs Delete&insertion [message #658381]

Mon, 12 December 2016 06:31

saipradyumn
Messages: 419
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

Report message to a moderator

Re: Update Vs Delete&insertion [message #658382 is a reply to message #658381]

Mon, 12 December 2016 06:37

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No it will not.

Report message to a moderator

Re: Update Vs Delete&insertion [message #658383 is a reply to message #658382]

Mon, 12 December 2016 06:40

Bill B
Messages: 1971
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.

Report message to a moderator

Re: Update Vs Delete&insertion [message #658386 is a reply to message #658383]

Mon, 12 December 2016 07:17

saipradyumn
Messages: 419
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?

Report message to a moderator

Re: Update Vs Delete&insertion [message #658388 is a reply to message #658386]

Mon, 12 December 2016 07:21

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Update Vs Delete&insertion [message #658389 is a reply to message #658388]

Mon, 12 December 2016 07:32

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: Update Vs Delete&insertion [message #658390 is a reply to message #658389]

Mon, 12 December 2016 07:38

saipradyumn
Messages: 419
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.

Report message to a moderator

Re: Update Vs Delete&insertion [message #658391 is a reply to message #658390]

Mon, 12 December 2016 07:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It appears you suffer from Compulsive Tuning Disorder.

Report message to a moderator

Re: Update Vs Delete&insertion [message #658393 is a reply to message #658386]

Mon, 12 December 2016 08:20

Michel Cadot
Messages: 68641
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

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thanks Mike for your valuable information .

Report message to a moderator

Previous Topic:	Bulk bind a single attribute of a collection
Next Topic:	Concatenating an array output into one single string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 11:27:02 CDT 2024