Optimize update query which uses MAX()

Question

This is question is for Oracle 19 DB. I need some help optimizing this update statement:

UPDATE T1 T1
   SET T1.COL1 = (SELECT MAX(T2.COL1)
                   FROM T2 T2
                   WHERE T2.COL2 = T1.COL2)
 WHERE T1.COL2 BETWEEN :1 AND :2
   AND T1.COL1 IS NULL
   AND EXISTS
 (SELECT 1
          FROM (SELECT MAX(T2.COL1) 
                  FROM T2 T2
                 WHERE T2.COL2 = T1.COL2) V1
         WHERE V1.COL1 IS NOT NULL)

I like this answer:

Massive UPDATE vs. MERGE performance on Oracle

and I tried doing similar but my problem is that my update has MAX() and I don't know how to optimize it.

Please provide an actual execution plan for the statement, collected via `dbms_xplan.display_cursor(format => '+PREDICATE ALLSTATS LAST')` (you need to add `gather_plan_statistics` hint to obtain Actual stats) or `dbms_sql_monitor.report_sql_monitor`. — astentx, Jul 12 '23 at 17:05

Littlefoot · Answer 1 · 2023-07-12T15:29:25.890

2

Merge, as you said:

merge into t1 
  using (select t2.col2, 
                max(t2.col1) max_col1
         from t2
         group by t2.col2
         having max(t2.col1) is not null
        ) x
  on (t1.col2 = x.col2)
  when matched then update set
    t1.col1 = x.max_col1
  where t1.col2 between :1 and :2
    and t1.col1 is null;

edited Jul 12 '23 at 15:29

answered Jul 12 '23 at 12:51

Littlefoot

131,892
15
35
57

1

You've missed `having max(t2.col1) is not null` (or `where t2.col1 is not null` since there are `group by` columns) inside the subquery to emulate `exists` predicate – astentx Jul 12 '23 at 14:10
Thank you. Unfortunately, this change from UPDATE to MERGE doesn't speed up executing in my case. Would adding an index on the COL1 help? (since I am only checking IS NULL / IS NOT NULL, not for some other values) – Dani Che Jul 12 '23 at 15:42
1

You're welcome. As of index: I'd expect index on COL2 (on both tables) to be useful. Also, consider gathering statistics on tables regularly so that Oracle optimizer sees relevant (not old) info. – Littlefoot Jul 12 '23 at 16:01

MT0 · Accepted Answer · 2023-07-12T15:09:09.263

1

Use a MERGE statement with a HAVING clause to perform the V1.COL1 IS NOT NULL filter (or, alternatively, a WHERE clause before the GROUP BY):

MERGE INTO T1
USING (
  SELECT col2,
         MAX(col1) AS col1
  FROM   t2
  -- WHERE col1 IS NOT NULL
  GROUP BY col2
  HAVING MAX(col1) IS NOT NULL
) t2
ON (t1.col2 = t2.col2)
WHEN MATCHED THEN
  SET   T1.COL1 = t2.col2
  WHERE T1.COL2 BETWEEN :1 AND :2
  AND   T1.COL1 IS NULL;

edited Jul 12 '23 at 15:09

answered Jul 12 '23 at 13:17

MT0

143,790
11
59
117

Thank you. Unfortunately, this change from UPDATE to MERGE doesn't speed up executing in my case. Would adding an index on the COL1 help? (since I am only checking IS NULL / IS NOT NULL, not for some other values) – Dani Che Jul 12 '23 at 14:51

Optimize update query which uses MAX()

2 Answers2