Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 07, 2017 - 11:03 am UTC

Last updated: July 08, 2017 - 1:26 am UTC

Version: 11g

Viewed 1000+ times

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions