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:
/*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;*/
select COUNT(1) into dupCount from entity_fact awhere 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 ) ;
/*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 ) ;*/
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 ) ;