title: merge statement using ROWID
body: <p>I have 3 foreign dimension keys I need to populate on a fact table.  I have tried using the following merge statement (using ROWID) to populate these three fields, however it is taking over 15 hours to run, so I am wondering what alternative approaches I can take that will be faster?  The fact table is around 1.8 million records and one of the dimensions (lookup function 3) is 900K records.  </p>
  
  <p>I have created 3 lookup functions to return the dimension keys.  Each of these functions goes from a field on the fact table and has to join to 2 or 3 tables to return the dim keys.  I have indexed the fields in the joins and the explain plan for each of them looks reasonable however it is still taking an extremely long time to run.  I am going to need to re-populate these dim keys each time the data is refreshed because unfortunately the source data is truncated and reloaded each time.  Any insight would be greatly appreciated!</p>
  
  <pre><code>MERGE INTO 
  (SELECT PRODUCT_DIM_KEY, TERRITORY_DIM_KEY, ACCOUNT_DIM_KEY , CUST_ID , PD_DT, DEXCOM_SKU_CD, ROWID rid 
  FROM FACT_TABLE
  ) ft1
  USING (select ROWID as rid
  FROM FACT_TABLE
   ) ft2
  ON (ft1.rid = ft2.rid)
  --join using ROWID
  WHEN MATCHED THEN UPDATE 
  SET ft1.PRODUCT_DIM_KEY  = PRODUCT_DIM_LOOKUP_FUNCTION(ft1.DEXCOM_SKU_CD),
  --lookup function 1
  ft1.TERRITORY_DIM_KEY = TERRITORY_DIM_LOOKUP_FUNCTION(ft1.CUST_ID ),
  --lookup function 2
  ft1.ACCOUNT_DIM_KEY = ACCOUNT_DIM_LOOKUP_FUNCTION(ft1.CUST_ID)
  --lookup function 3
  </code></pre>
  
  <p>--Lookup function 1
  --columns indexed: pr.PRODUCT_SKU</p>
  
  <pre><code>BEGIN
      SELECT PRODUCT_DIM_KEY INTO v_dim_id
      FROM   PRODUCT_DIM pr 
      WHERE  pr.PRODUCT_SKU          = p_product_code
      AND    pr.PRODUCT_DELETED_FLAG = 'N';
  </code></pre>
  
  <p>--Lookup function 2
  --columns indexed: addr.EXTRNL_CUST_ID, addr.PSTL_CD</p>
  
  <pre><code>BEGIN
      SELECT TERRITORY_DIM_KEY
    INTO v_dim_id
    FROM ADDR_DIM addr,
         ZIP_CODE_DIM zip,
         TERRITORY_DIM terr
   WHERE     p_hcp_code = addr.EXTRNL_CUST_ID
         AND addr.BEST_REC_IND = 1
         AND SUBSTR (addr.PSTL_CD, 1, 5) = zip.ZIP_CODE
         AND zip.ACTIVE_FLAG = 'Y'
         AND terr.TERRITORY_CODE = zip.TERRITORY_CODE;
  </code></pre>
  
  <p>--Lookup function 3
  --columns indexed: ccv.VAL_1_ID, ac.IMS_ID, ac.ACTIVE_FLAG</p>
  
  <pre><code>BEGIN
  SELECT ACCOUNT_DIM_KEY
    INTO v_dim_id
    FROM CCV_DIM ccv, 
         ACCOUNT_DIM ac
   WHERE     p_hcp_code = ccv.CUST_ID
         AND ccv.SCNDY_ID_TYP_XID = 202325
         AND ccv.VAL_1_ID = ac.IMS_ID
         AND ac.ACTIVE_FLAG = 'Y'
  </code></pre>