Hi, I'm a bit new to the development of plsql.
I would like to know how I could optimize the delete operation with a forall if my query is the following:
DELETE FROM SCH.TA_DELETE
WHERE FIACUM < 1
AND FIPAIS = 1
AND FIPROC = 24
AND FIANIOPROC = 2018;
DELETE FROM SCH.TA_DELETE
WHERE FIPERATRAACUM > 55
AND FIPAISID <> 1
AND FISEMPROCESO = 23
AND FIANIOPROCESO = 2018;
I have indexes in the columns FIPAIS, FIPROC, FIANIOPROC.
The values are not extracted from other tables.
it is advisable to create a forall for each instruction delete?
Thank you very much for your help.
FORALL is an optimization designed to improve *multiple* delete operations. For example, if I had a list of 100 numbers to delete, I could run 100 delete statements, or I could use a FORALL to delete them in a single delete statement.
So that is not going to assist in the scenario you have presented here. Your two delete statements don't need any modification in terms of SQL, but if they are running slowly, then we need to track why.
The performance is made of two parts
A- time to find the rows
B- time to remove the rows
You can get a rough estimate of the (A) by re-phrasing them as a SELECT statement making sure you select enough columns do that the optimizer is unlikely to come up with a plan *different* the delete. Hence something like:
select count(A_NON_INDEXED_COLUMN)
from SCH.TA_DELETE
WHERE FIACUM < 1
AND FIPAIS = 1
AND FIPROC = 24
AND FIANIOPROC = 2018;
and see how that runs.
If it is a large percentage of the overall delete time, then it is the finding of the rows you need to improve - you might need to alter indexes etc.
If it is a small percentage of the overall delete time, then the deletion cost is large - that could be - triggers, locking issues, too many indexes to update, lots of constraints to check etc.
But narrow down the cause, and then work from there.