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?
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;