oracle "merge into" too slow-CodePudding

i'm trying to update a column in a table using the id of another table only if one or two field match each other. Sadly the query run very slowly and i don't understand why. PS:(the checked fields for table A may be null or have leading/trailing empty spaces )

MERGE INTO B B1
USING (
    SELECT B2.LUSERINVENTORYID LUSERINVENTORYID, a1.lastid lastid
      FROM B B2,
           (SELECT lastid,
                   TRIM(UPPER(serialno)) AS serialno,
                   TRIM(UPPER(barcode)) AS barcode
              FROM A) a1
     WHERE (B2.loaded_serialno = a1.serialno AND B2.loaded_barcode = a1.barcode) 
        OR (B2.loaded_serialno = a1.serialno AND  B2.loaded_barcode IS NULL) 
        OR (B2.loaded_serialno IS NULL       AND  B2.loaded_barcode = a1.barcode) 
   ) res
ON (B1.luserinventoryid = res.luserinventoryid)
WHEN MATCHED THEN
UPDATE SET B1.lassetinvolvedid = res.lastid

please somebody can tell me how i can improve the execution time of this merge?

CodePudding user response：

Without looking at your execution plan or knowing your data, we can only guess. That being said, at first glance I can tell you that you are almost certain to have problems stemming from those OR clauses in your join. If you can rewrite this to use a definite join column instead of all these conditions, you'll be much better off.

If you can't, you may also try the hint /* use_concat */ and Oracle might rewrite it as three UNION ALL sets with a single-column definite join in each one, which is basically rewriting it for you.

MERGE INTO b b1
USING (
        SELECT /*  use_concat */ b2.id id, a1.id lastid
          FROM b b2,
               (SELECT a1.id,
                       TRIM(UPPER(a1.serialno)) AS serialno,
                       TRIM(UPPER(a1.barcode)) AS barcode
                  FROM a) a1
         WHERE (b2.loaded_serialno = a1.serialno AND b2.loaded_barcode = a1.barcode) 
            OR (b2.loaded_serialno = a1.serialno AND  b2.loaded_barcode IS NULL) 
            OR (b2.loaded_serialno IS NULL       AND  b2.loaded_barcode = a1.barcode) 
       ) res
  ON (a1.luserinventoryid = res.luserinventoryid)
WHEN MATCHED THEN
  UPDATE SET b1.lassetinvolvedid = res.lastid;

CodePudding user response：

Without your data it is difficult to determine but you appear to perform a self-join on B in the ON clause of the merge and if there is a 1-to-1 correspondence (i.e. you are joining on a field with a UNIQUE key) then you could possibly skip that and merge A and B directly:

MERGE INTO B
USING (
  SELECT lastid,
         TRIM(UPPER(serialno)) AS serialno,
         TRIM(UPPER(barcode)) AS barcode
  FROM   A
) A
ON (  (B.loaded_serialno = a.serialno AND B.loaded_barcode = a.barcode) 
   OR (B.loaded_serialno = a.serialno AND B.loaded_barcode IS NULL) 
   OR (B.loaded_serialno IS NULL      AND B.loaded_barcode = a.barcode)
   )
WHEN MATCHED THEN
  UPDATE SET B.lassetinvolvedid = A.lastid;