UPDATE query is slow in combination with RETURNING INTO clause

Question

I have update query which returns updated rows ID. Execution time of query is about 90 seconds. When i remove Returning clause, then execution time is 1ms. Table update_table has 39000 rows. Query updates 0 rows in this case. When updates 3 rows- execution time is same.

DECLARE
  type intTable IS TABLE OF INTEGER;
  idCol intTable;
BEGIN
UPDATE 
  update_table
            SET  
            prop1 = 3, prop2 = NULL
            WHERE EXISTS (
                SELECT null FROM update_table f 
                    INNER JOIN rel_table1 u ON f.ID= u.ID
                    INNER JOIN rel_table2 VP ON f.another_ID = VP.another_ID
                WHERE (u.prop1 = 3) 
                    AND VP.prop1 = 1
                    AND (u.prop2 = 75)
                    AND f.ID = update_table.ID
            )
         ReTURNING ID BULK COLLECT INTO idCol;
.
.
.
  END;

Why returning clause slows down query?

Answer 1

A good part of using Oracle is knowing what is "supposed" to happen and what isn't.

Adding a RETURNING INTO clause is not "supposed" to make your update run more slowly. When something happens that isn't supposed to happen, check Oracle's support site to see whether it is a known bug.

In your case, it looks like you are encountering:

Bug 27131648 - SUB OPTIMAL PLAN ON UPDATE STATEMENT WITH RETURNING INTO

I am not sure if there is a patch, but there is a simple workaround: use the UNNEST hint. In your case, that would be:

UPDATE 
  update_table
            SET  
            prop1 = 3, prop2 = NULL
            WHERE EXISTS (
                SELECT /*+ UNNEST */ null FROM update_table f 
                    INNER JOIN rel_table1 u ON f.ID= u.ID
                    INNER JOIN rel_table2 VP ON f.another_ID = VP.another_ID
                WHERE (u.prop1 = 3) 
                    AND VP.prop1 = 1
                    AND (u.prop2 = 75)
                    AND f.ID = update_table.ID
            )
         ReTURNING ID BULK COLLECT INTO idCol;