Let's say I have a statement, dedupe_sql that performs a deduplication routine on a certain set of records.

I can output which keys have had their duplicates removed like this:

DECLARE
  dedupe_sql   VARCHAR2( 4000 BYTE ) := '...The details are not important...';
  deleted_keys DBMS_SQL.VARCHAR2_TABLE
BEGIN
  EXECUTE IMMEDIATE dedupe_sql
    RETURNING key BULK COLLECT INTO deleted_keys;

  FOR key_index IN 1 .. deleted_keys.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE( 'Deleted: ' || deleted_keys( key_index ) )
  END LOOP;
END;

But now let's say that each key could have multiple duplicates removed, and for auditing purposes, my output needs to display each removed key only once, followed by the number of occurrences that I removed.

And let's say that the input data set contains a huge volume of data. (And so, potentially, does the set of rows to be deleted.)

Is there any Oracle construct that would allow me to BULK COLLECT the results of my delete operation, and then run a COUNT/GROUP BY aggregate function on the result?

I have thought of creating a table to hold the results, which I would then drop when I no longer needed it, but I am wondering if there's a good way to do this in memory instead.

Many thanks!

CodePudding user response：

Generally, a regular delete would always have better performance than a bulk delete and the only reason to use a bulk delete is if you can't make the delete in a single statement.

I may misunderstand your implication, however I would suggest to do the grouping with and counting as a select. The select will give you the same scope if you just want to log some statistics and then delete that scope.