CODEWITHSUNDEEP
oracle-databaseoracle12c
Firrel
Firrel

Reputation: 23

UPDATE query is slow in combination with RETURNING INTO clause

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?

Upvotes: 2

Views: 130

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17934

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;

Upvotes: 3

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

I would recommend splitting it into two parts, first BULK COLLECT and next FORALL collected ID's, both extremely fast and you'll keep being able to further reference updated ID's from idCol.

DECLARE
  type intTable IS TABLE OF INTEGER;
  idCol intTable;
BEGIN
    SELECT f.id 
      BULK COLLECT INTO idCol
      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);

    FORALL indx IN 1..idCol.COUNT
        UPDATE update_table
           SET prop1 = 3, prop2 = NULL
         WHERE id = idCol(indx);

.
.
.
END;

I hope I helped!

Upvotes: 1

Related Questions