Abhaysk, Thanks for the suggestion. I like the way you have written the new query - unfortunately this one is running for more than 50 secs(and it is still running) with appr. 100% CPU usage throughout.
There are about 3004 corr.content_id and 49034 ci.locator. So when you do a cartesian join between these two, it is producing 14,72,98,136 rows to which we are applying the CASE equation.
Here is the explain plan for your query :
Yes you are right in assuming that index CSI.IDX_CORR_ASSET_CONTENT_MLO is on CLG_CORRELATION.asset_type_id.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 4 820 39 MERGE JOIN CARTESIAN 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 BUFFER SORT 1 24 33 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 28 980 4
Reply With Quote