SELECT   '1', 'U' AS update_status_ind, 'OLD', hist.transaction_date, alcm.limit_charge_map_id,
         alcm.charge_id, alcm.charge_detail_id, sc.sci_las_sec_id,
         hist.reference_id AS cms_collateral_id, sci_security_dtl_id, sc.charge_nature,
         sc.charge_amount, sc.charge_currency_code, sc.le_charge, sc.le_charge_date,
         sc.legal_charge_date, sc.le_juridict, sc.le_juridict_date, sc.le_govnlaw,
         sc.le_govnlaw_date,
         (CASE
            WHEN sc.prior_charge_currency IS NULL
              THEN NULL
            ELSE sc.prior_charge_amount
          END
         ) prior_charge_amount,
         (CASE
            WHEN sc.prior_charge_amount IS NULL
              THEN NULL
            ELSE sc.prior_charge_currency
          END
         ) prior_charge_currency,
         sc.security_rank, sc.prior_chargee, sc.prior_charge_type, sc.charge_type,
         sc.confirm_charge_date, alsm.cms_lsp_appr_lmts_id, alsm.sci_las_sys_gen_id,
         alsm.sci_las_llp_id, alsm.sci_las_lsp_id, alsm.sci_las_le_id, alsm.sci_las_lmt_id,
         alsm.sci_las_sec_id, alsm.las_pledgor_id
    FROM trans_history hist,
         cms_stage_security col,
         cms_stage_limit_charge_map slcm,
         cms_stage_limit_security_map slsm,
         cms_stage_charge_detail sc,
         cms_limit_charge_map alcm,
         cms_limit_security_map alsm,
         cms_charge_detail ac,
         (SELECT   MAX (HISTORY.transaction_date) transaction_date, HISTORY.transaction_id
            FROM trans_history HISTORY,
				 cms_stage_security col_stage,
                 (SELECT   MAX (h.transaction_date) last_trx_date, h.transaction_id
                      FROM trans_history h, cms_security c
                     WHERE h.transaction_date >= TO_DATE (:ctrl1dt, 'dd/mm/yyyy HH24:MI:SS')
                       AND h.transaction_date < TO_DATE (:ctrl2dt, 'dd/mm/yyyy HH24:MI:SS')
                       AND c.cms_collateral_id = TO_NUMBER (h.reference_id)
                       AND c.is_security_perfected = 'N'  
                       AND c.status NOT IN ('DELETED', 'PENDING_DELETE')
					   AND h.opsdesc NOT IN ('PART_DELETE', 'FULL_DELETE')                      
                       AND h.from_state = 'PENDING_UPDATE'
                       AND h.status = 'ACTIVE'
                       AND h.transaction_type = 'COL'
                  GROUP BY h.transaction_id) 
				  LAST_TRX_N
           WHERE HISTORY.transaction_id = LAST_TRX_N.transaction_id             
             AND HISTORY.from_state <> 'ACTIVE'             
             AND HISTORY.opsdesc NOT IN ('PART_DELETE', 'FULL_DELETE')
             AND HISTORY.status = 'ACTIVE'             
             AND (   (TRUNC (HISTORY.transaction_date) < TRUNC (LAST_TRX_N.last_trx_date)
                     )
                  OR (    TRUNC (HISTORY.transaction_date) = TRUNC (LAST_TRX_N.last_trx_date)
                      AND NOT EXISTS (
                             SELECT 1
                               FROM trans_history HISTORY1
                              WHERE HISTORY1.transaction_id =  LAST_TRX_N.transaction_id
                                AND HISTORY1.transaction_type = 'COL'
                                AND TRUNC (HISTORY1.transaction_date) < TRUNC (LAST_TRX_N.last_trx_date))
                     )
                 )
             AND HISTORY.staging_reference_id = col_stage.cms_collateral_id
             AND col_stage.is_security_perfected = 'Y'
        GROUP BY HISTORY.transaction_id )
		PREV_LAST_TRX
 WHERE hist.transaction_id = PREV_LAST_TRX.transaction_id
     AND hist.transaction_date = PREV_LAST_TRX.transaction_date   
     AND col.cms_collateral_id = TO_NUMBER (hist.staging_reference_id)     
     AND col.cms_collateral_id = slcm.cms_collateral_id
     AND slsm.charge_id = slcm.charge_id
     AND sc.charge_detail_id = slcm.charge_detail_id
     AND alsm.cms_lsp_appr_lmts_id = slsm.cms_lsp_appr_lmts_id
     AND alcm.cms_lsp_appr_lmts_id = slcm.cms_lsp_appr_lmts_id
     AND alsm.cms_collateral_id = TO_NUMBER (hist.reference_id)
     AND alsm.charge_id = alsm.charge_id
     AND ac.charge_detail_id = alcm.charge_detail_id
     AND ac.cms_ref_id = sc.cms_ref_id
     AND col.status NOT IN ('DELETED', 'PENDING_DELETE')
     AND (ac.status <> 'DELETED' OR ac.status IS NULL)
     AND alcm.status <> 'DELETED'
     AND slcm.status <> 'DELETED'
     AND alsm.update_status_ind <> 'D'
     AND slsm.update_status_ind <> 'D'

Operation																							Object Name												Rows	Bytes		Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE																									1  	73  	 	      	             	 
  NESTED LOOPS																																					1  	305  	73  	 	      	             	 
    NESTED LOOPS																																				1  	238  	72  	 	      	             	 
      NESTED LOOPS																																			1  	211  	71  	 	      	             	 
        MERGE JOIN CARTESIAN																														1  	168  	69  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID								  CMS_R14_DEV.TRANS_HISTORY					1  	35  	3  	 	      	             	 
            NESTED LOOPS																																1  	68  	53  	 	      	             	 
              HASH JOIN																																	1  	33  	50  	 	      	             	 
                VIEW																																		1  	21  	20  	 	      	             	 
                  SORT GROUP BY																													1  	98  	20  	 	      	             	 
                    FILTER		  	 	 	 	      	             	 
                      NESTED LOOPS																											1  	98  	18  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID		CMS_R14_DEV.TRANS_HISTORY					1  	75  	17  	 	      	             	 
                          INDEX RANGE SCAN						CMS_R14_DEV.IDX_TEST1							1K	7  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID		CMS_R14_DEV.CMS_SECURITY					1  	23  	1  	 	      	             	 
                          INDEX UNIQUE SCAN						CMS_R14_DEV.SYS_C001217											1		32 K	 	 	 	      	             	 
                VIEW																																		1  	12  	29  	 	      	             	 
                  SORT GROUP BY																													1  	90  	29  	 	      	             	 
                    FILTER		  	 	 	 	      	             	 
                      NESTED LOOPS																											1  	90  	28  	 	      	             	 
                        NESTED LOOPS																										1  	78  	27  	 	      	             	 
                          VIEW																													1  	23  	20  	 	      	             	 
                            SORT GROUP BY																								1  	98  	20  	 	      	             	 
                              FILTER		  	 	 	 	      	             	 
                                NESTED LOOPS																						1  	98  	18  	 	      	             	 
                                  TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.TRANS_HISTORY	1  	75  	17  	 	      	             	 
                                    INDEX RANGE SCAN	CMS_R14_DEV.IDX_TEST1							1 K	 	7  	 	      	             	 
                                  TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_SECURITY	1  	23  	1  	 	      	             	 
                                    INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012171					32 K	 	 	 	      	             	 
                          TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.TRANS_HISTORY					1  	55  	7  	 	      	             	 
                            INDEX RANGE SCAN	CMS_R14_DEV.IDX_TR_HIST_1									25  	 	2  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_STAGE_SECURITY			1  	12  	1  	 	      	             	 
                          INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012388										100  	 	 	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID		CMS_R14_DEV.TRANS_HISTORY						1		25  	2.50964819384571  	 	      	             	 
                        BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
                          BITMAP AND		  	 	 	 	      	             	 
                            BITMAP INDEX SINGLE VALUE	CMS_R14_DEV.IDX_TR_HIST_2	  	 	 	 	      	             	 
                            BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
                              INDEX RANGE SCAN	CMS_R14_DEV.IDX_TR_HIST_1								25  	 	3  	 	      	             	 
              INDEX RANGE SCAN	CMS_R14_DEV.IDX_TEST1																		2  	 	2  	 	      	             	 
          BUFFER SORT																																		1  	100  	66  	 	      	             	 
            TABLE ACCESS FULL	CMS_R14_DEV.CMS_CHARGE_DETAIL															1  	100  	16  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_LIMIT_CHARGE_MAP										1  	43  	2  	 	      	             	 
          INDEX RANGE SCAN	CMS_R14_DEV.IDX_LMT_CHGE_MAP_01															5  	 			1  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_SECURITY															1  	27  	1  	 	      	             	 
        INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012171																			5  	 	 	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_LIMIT_SECURITY_MAP											1  	67  	1  	 	      	             	 
      INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012031																				20