11 Replies Latest reply: Apr 17, 2014 3:14 AM by _Karthick_ RSS

    Need help for huge table load

    user507531

      Oracle 11G R2

       

      We need to insert data in huge_main_fact from monthly_fact table

      One month data loads quickly however when the data grows in huge_main_fact MERGE is getting slower

      Can you please suggest any changes to it ?

       

      MERGE /*+ parallel(huge_main_fact,3) */ INTO huge_main_fact tgt USING (

      SELECT /*+ parallel(monthly_fact,3) full (monthly_fact) */ SERVICE_PROVIDER_ID , UNIT_ID, PRODUCT_ID , MONTH_ID LAST_ORDER_MONTH

      FROM monthly_fact ) src

      ON (src.SERVICE_PROVIDER_ID = tgt.SERVICE_PROVIDER_ID

      and src.UNIT_ID = tgt.UNIT_ID

      and src.PRODUCT_ID = tgt.PRODUCT_ID )

      WHEN MATCHED THEN UPDATE SET tgt.LAST_ORDER_MONTH =src.LAST_ORDER_MONTH

      WHEN NOT MATCHED THEN INSERT ( SERVICE_PROVIDER_ID , UNIT_ID, PRODUCT_ID , LAST_ORDER_MONTH)

      VALUES ( src.SERVICE_PROVIDER_ID , src.UNIT_ID, src.PRODUCT_ID , src.LAST_ORDER_MONTH);

       

      huge_main_fact --> Huge fact where we need to add 3 years data

      monthly_fact --> Monthly fact - it has montly data

       

       

      SERVICE_PROVIDER_ID - Service provider id

      UNIT_ID - Production units - total 15000 production units

      PRODUCT_ID - Product id

      LAST_ORDER_MONTH - When product was last ordered

       

      Due to some reason we can not use partitions