You Asked
Hi Connor/Chirs,
I tried executing below two statements to delte records from tb_test, and the statement with FORALL delete works faster?
EXECUTE IMMEDIATE 'DELETE FROM tb_test WHERE ROWID IN (SELECT column_value FROM TABLE(CAST(:tb_rowid as VC_ARRAY)))' USING tb_rowid;
FORALL i in tb_rowid.first .. tb_rowid.last
DELETE FROM tb_TEST WHERE ROWID = tb_rowid(i);
FORALL works faster than normal DELETE command.
Since I am deleting using ROWID, does it really matter (in terms of performance) to go with DELETE statment or FORALL delete?
and Connor said...
Without seeing the execution plan, and possibly trace metrics we can't really say.
*Potentially* the first one reverted to a full table scan because the estimate for how many rows coming out from the table() function was incorrect. Of course, a full table scan *might* be the best option if the number of rows is large.
Try comparing the following:
EXECUTE IMMEDIATE 'DELETE /*+ rowid(tb_tes) */ FROM tb_test WHERE ROWID IN (SELECT column_value FROM TABLE(CAST(:tb_rowid as VC_ARRAY)))' USING tb_rowid;
with your FORALL and see if they are comparable.
Is this answer out of date? If it is, please let us know via a Comment