Thanks for the question, Lesley.
Asked: September 28, 2016 - 7:05 pm UTC
Last updated: September 29, 2016 - 1:15 pm UTC
Version: 12c
Viewed 1000+ times
You Asked
We are upgrading software that executes against an Oracle database. The vendor modified code to add a Select statement before issuing an existing Delete statement. The criteria of both commands were the same except the Select included a table that only has 1 row it. The table they are selecting/deleting from is very large and is being inserted to/deleted from frequently in this program.
The run times increased dramatically. When we asked why they added this Select statement, they indicated that the Select was much faster than a Delete when no rows meet the Delete criteria.
In our case - the performance took a nose dive and we ended up taking the Select out. The run times went back to normal after we made the change.
We can do more analysis with our various diagnostic tools to see what is going on with the Select statement. However, I was just very curious to find out if their reasoning is accurate. Is issuing a Delete when no rows are there to delete that much less efficient than issuing a select? Running both ways in SQL Developer do not indicate that the select is faster. In my rudimentary testing, it seemed like the Delete was just as fast.
Please Oh Mighty Tom - what do you think???
and Connor said...
Putting aside performance, it is just a fundamentally flawed approach.
If you do:
select the row
- if found, then delete
- if not found, then skip delete
then you've guaranteed nothing, because between the select and the delete, anyone could have inserted a row, so your application takes the wrong path.
Alternatively, if you do
select the row and lock it
- if found, then delete
- if not found, then skip delete
Then your logic is now correct, but you've just done *exactly* what a delete would do, ie, locate the row, lock it, and then delete it...but you've done it with 2 statements not 1.
Just do the delete.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment