Need help tuning query - left join using concatenated fields not efficient

Question

I've written a query where I'm filtering out records from a population where values of 2 fields in a nested query are equal to 2 corresponding fields in a table. I'm trying to use the concatenation of the 2 key fields as a lookup.

While this does get me the results I'm looking for it seems that this method is pretty inefficient as the query takes around 60 seconds to return results (the CRM.ASSET_PLUS table has over 10M records). Assuming I can't filter anything out and without creating a custom field in CRM.ASSET_PLUS with those 2 fields already concatenated.

Is there a better way I can being doing this?

SELECT DISTINCT A.ACCOUNT_NUMBER
  FROM (SELECT C.ACCOUNT_NUMBER,
               A.LOCATION_ACCOUNT_ID || A.PRODUCT_ID AS LOOKUP
          FROM CRM.ASSET_PLUS A,
               CRM.PROD_INT B,
               CRM.ACCOUNT C,
               CRM.ACCOUNT D
         WHERE A.PRODUCT_ID = B.ROW_ID
           AND A.LOCATION_ACCOUNT_ID = C.ACCOUNT_ID
           AND C.PARENT_ID = D.ACCOUNT_ID
           AND D.ACCOUNT_NUMBER = '00750333'
       ) A
   LEFT JOIN (SELECT A.LOCATION_ACCT_ID || A.PRODUCT_ID AS LOOKUP
                FROM ORDER_FORM_PRODUCTS A
               WHERE A.AGREEMENT_NUM = '00750333_JUN2014'
             ) B
     ON A.LOOKUP = B.LOOKUP
  WHERE B.LOOKUP IS NULL;

Answer 1

Instead of concatenating try joining on multiple fields

SELECT DISTINCT
    A.ACCOUNT_NUMBER
FROM 
    CRM.ASSET_PLUS A,
    CRM.PROD_INT B,
    CRM.ACCOUNT C,
    CRM.ACCOUNT D
LEFT JOIN ORDER_FORM_PRODUCTS E 
    ON (E.LOCATION_ACCOUNT_ID = A.LOCATION_ACCOUNT_ID
       OR (E.LOCATION_ACCOUNT_ID IS NULL AND A.LOCATION_ACCOUNT_ID IS NULL))
    AND (E.PRODUCT_ID = A.PRODUCT_ID 
        OR (E.PRODUCT_ID IS NULL AND A.PRODUCT_ID IS NULL))
    AND E.AGREEMENT_NUM = '00750333_JUN2014'
WHERE 
    A.PRODUCT_ID = B.ROW_ID 
    AND A.LOCATION_ACCOUNT_ID = C.ACCOUNT_ID
    AND C.PARENT_ID = D.ACCOUNT_ID
    AND D.ACCOUNT_NUMBER = '00750333'
    AND E.LOCATION_ACCOUNT_ID IS NULL
    AND E.PRODUCT_ID IS NULL

For clarity I recommend making your joins explicit

SELECT DISTINCT
    A.ACCOUNT_NUMBER
FROM 
    CRM.ASSET_PLUS A
    JOIN CRM.PROD_INT B ON A.PRODUCT_ID = B.ROW_ID
    JOIN CRM.ACCOUNT C ON A.LOCATION_ACCOUNT_ID = C.ACCOUNT_ID
    JOIN CRM.ACCOUNT D ON C.PARENT_ID = D.ACCOUNT_ID
        AND D.ACCOUNT_NUMBER = '00750333'
    LEFT JOIN ORDER_FORM_PRODUCTS E 
       ON (E.LOCATION_ACCOUNT_ID = A.LOCATION_ACCOUNT_ID
         OR (E.LOCATION_ACCOUNT_ID IS NULL AND A.LOCATION_ACCOUNT_ID IS NULL))
       AND (E.PRODUCT_ID = A.PRODUCT_ID 
         OR (E.PRODUCT_ID IS NULL AND A.PRODUCT_ID IS NULL))
       AND E.AGREEMENT_NUM = '00750333_JUN2014'
WHERE 
    E.LOCATION_ACCOUNT_ID IS NULL
    AND E.PRODUCT_ID IS NULL