Hello everyone,
We have a relatively big table (about 18 millions rows) in which we want to eliminate duplicate rows based on several criteria.
One of these criteria is based on the equality of the values in that column or the fact that this column IS NULL on either side.
Originally we had this code:
DELETE FROM TAB_T A ...
WHERE EXISTS (SELECT 'x' FROM TAB_T B
WHERE ... AND
(A.COLC = B.COLC OR A.COLC IS NULL OR B.COLC IS NULL) AND
...
This DELETE statement took approx. 24 minutes to run.
We tried to optimize this DML using the COALESCE function this way:
DELETE FROM TAB_T A ...
WHERE EXISTS (SELECT 'x' FROM TAB_T B
WHERE ... AND
COALESCE(A.COLC, B.COLC) = COALESCE(B.COLC, A.COLC) AND
...
This DELETE statement took approx. 13 minutes to run. But we were missing one case : when A.COLC IS NULL and B.COLC IS NULL because NULL = NULL won't return TRUE.
We modified the COALESCE to add a third "hardcoded" value that we were sure this value does not exists for that column.
So we rewrite the DELETE statement this way:
DELETE FROM TAB_T A ...
WHERE EXISTS (SELECT 'x' FROM TAB_T B
WHERE ... AND
COALESCE(A.COLC, B.COLC, -1) = COALESCE(B.COLC, A.COLC, -1) AND
...
This DELETE statement took approx. 40 minutes to run !!!
What could explain the huge difference in execution time between the use of the COALESCE function with 2 expressions vs 3 expressions?
Have someone ever experience something similar?
Best regards.
Carl