Quote Originally Posted by tamilselvan
Do you see MERGE JOIN CARTESIAN operation in the plan?

Otherwise, try another hint:
USE_NL(U, cai, car, cir, ci, corr, cbs)

Tamil

Here is the expalin plan with the above hint.
Code:
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	39  	 	      	             	 
  SORT AGGREGATE		1  	205  	 	 	      	             	 
    NESTED LOOPS		1  	205  	39  	 	      	             	 
      NESTED LOOPS		1  	170  	35  	 	      	             	 
        NESTED LOOPS		3  	438  	29  	 	      	             	 
          NESTED LOOPS		3  	237  	23  	 	      	             	 
            NESTED LOOPS		1  	38  	20  	 	      	             	 
              NESTED LOOPS		3  	84  	14  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_RESULT	3  	54  	11  	 	      	             	 
                  INDEX RANGE SCAN	CSI.IDX_ASMNTRES_COMPDATE	38  	 	3  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_INSTANCE	1  	10  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	CSI.PK_ASSESSMENT_INSTANCE	1  	 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CSI.CLG_USER	1  	10  	2  	 	      	             	 
                INDEX UNIQUE SCAN	CSI.PK_USER	1  	 	1  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM_RESULT	13  	533  	3  	 	      	             	 
              INDEX RANGE SCAN	CSI.PK_ITEM_RESULT	13  	 	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM	1  	67  	2  	 	      	             	 
            INDEX UNIQUE SCAN	CSI.PK_ITEM	1  	 	1  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CSI.CB_STANDARD	1  	24  	2  	 	      	             	 
          INDEX RANGE SCAN	CSI.UK_CB_STANDARD_LOCATOR	1  	 	1  	 	      	             	 
      INDEX RANGE SCAN	CSI.IDX_CORR_MLO_CONTENT_ASSET	1  	35  	4
The Merge Cartesian Join is gone but the execution time has increased to 4.4 mins.

Thanks.