Tech Tip: HOTFIX for ‘Data Migration takes too long due to the large data volume in the ’ENTITY_FACT’ table (DE245515)

Document created by biecl01 Employee on Feb 21, 2017
Version 1Show Document
  • View in full screen mode

Problem: The database migration takes a long time to complete due to a huge amount of data stored in the ENTITY_FACT table. In such a scenario, the queries that are currently used for migration take up a lot of resources and are not efficient.

 

Solution: The following manual update fixes the Load_Metrics package and enhances the Dedup_Entity_Fact procedure. Once you deploy the updated package on your system, the migration process will be faster. Follow these steps:

  1. Open SQL Developer.
  2. Go to Package and open the Load_Metrics PACKAGE body.
  3. Go to the Dedup_Entity_Fact procedure.
  4. Comment out (disable) the following query by entirely enclosing it in comment signs:
     /*SELECT COUNT(*) into dupCount
    FROM ( SELECT 1 FROM ENTITY_FACT A
    WHERE A.GMT_METRIC_DATE BETWEEN (SELECT MIN(GMT_METRIC_DATE) FROM ENTITY_FACT_COMB_TMP )  AND (SELECT MAX(GMT_METRIC_DATE) FROM ENTITY_FACT_COMB_TMP )
    AND EXISTS ( SELECT 1 FROM ENTITY_FACT_COMB_TMP B
                                WHERE A.GMT_METRIC_DATE = B.GMT_METRIC_DATE
                                 AND A.ENTITY_DIM_ID = B.ENTITY_DIM_ID ) ) A
    WHERE ROWNUM = 1;

    */
  5. Manually add or copy and paste the following query:
    select COUNT(1) into dupCount from entity_fact a
    where exists ( select 1 from ENTITY_FACT_COMB_TMP b
                            where  a.gmt_metric_date = b.gmt_metric_date
                            and a.entity_dim_id = b.entity_dim_id
                            and a.metric_id = b.metric_id
                            and a.source_type = b.source_type ) ;
    To facilitate tracking, consider adding a clarifying remark to the new query.
  6. Comment out (disable) the following query by entirely enclosing it in comment signs:
     /*delete from
             entity_fact a
             where
             a.gmt_METRIC_DATE BETWEEN
               (select min(gmt_metric_date)  from ENTITY_FACT_COMB_TMP   )
               AND
               (select max(gmt_metric_date)  from ENTITY_FACT_COMB_TMP   )
             and
             exists
             ( select 1
               from
               ENTITY_FACT_COMB_TMP b
               where
               a.gmt_metric_date = b.gmt_metric_date
               and a.entity_dim_id = b.entity_dim_id
               and a.metric_id = b.metric_id
               and a.source_type = b.source_type
              ) ;
    */
  7. Manually add or copy and paste the following query:
    delete from entity_fact a
               where exists ( select 1 from ENTITY_FACT_COMB_TMP b
                      where a.gmt_metric_date = b.gmt_metric_date
                      and a.entity_dim_id = b.entity_dim_id
                      and a.metric_id = b.metric_id
                      and a.source_type = b.source_type

                               ) ;   
    To facilitate tracking, consider adding a clarifying remark to the new query.
  8. Open the Compile tool and compile the updated package.
  9. Verify that the deployment was successful:
    1. Connect to the SQL Developer.
    2. Go to Package and open the Load_Metrics PACKAGE body.
    3. Go to the Dedup_Entity_Fact procedure and check if the updated sql queries are available.
    4. Verify that Load_Metrics is compiled without errors.

Attachments

    Outcomes