Sql query is like this
SELECT a.*, ROWNUM AS rnm
FROM ( SELECT A.F_PROC_ID, C.L_NAME
FROM TABLE_1 A, – has 473578 records
TABLE_2 B, – has 829 records
TABLE_3 C, – has 8304 records
TABLE_3 D,
TABLE_4 E, – has 22674 records
TABLE_4 F
WHERE B.STATUS_T_ID = 1
AND A.STATUS_ID = 2
AND A.STATUS_ID = B.STATUS_ID
AND A.F_PROC_ID = E.PROC_ID
AND A.S_PROC_ID = F.PROC_ID
AND C.L_DOM_ID = 151
AND A.CI_CD = C.L_VALUE_CODE
AND D.L_DOM_ID = 236
AND A.STMNT_CD = D.L_VALUE_CODE
AND C.ACTIVE_FLAG = ‘A’
AND D.ACTIVE_FLAG = ‘A’
ORDER BY C.L_NAME ASC
) A
WHERE ROWNUM < 200;
Explain plan from Toad is in attched file
inner query is returning 380896 records on which order by operation is performing to get only first 200 sorted records.
There is index on C.L_NAME column but explain plan is not showing it, i used INDEX hint but still it didn’t get used.
with ORDER BY clause it is taking 30 sec
without ORDER BY clause it takes 1 sec.
Any suggestion on how to tune it to execute it within 2 sec with this ORDER BY
explain_plan.txt (2.37 KB)
According to your plan, the output “Cardinality: 3” is wrongly estimated by Oracle, it should be 380896 records or a cost number, you can paste the inner query with “Order By” to our SQL optimizer to find a better elasped time alternative rewrite and put it back into the query, Let me know the result, if it can solve your problem or not, I will see if there any other solutions if it can’t solve the problem.
Richard
Plan
SELECT STATEMENT ALL_ROWS Cost: 1,647 Bytes: 567 Cardinality: 3
22 COUNT STOPKEY
21 VIEW SCHEMA_NAME. Cost: 1,647 Bytes: 567 Cardinality: 3
20 SORT ORDER BY STOPKEY Cost: 1,647 Bytes: 558 Cardinality: 3
19 NESTED LOOPS
17 NESTED LOOPS Cost: 1,646 Bytes: 558 Cardinality: 3
15 NESTED LOOPS Cost: 1,643 Bytes: 516 Cardinality: 3
12 NESTED LOOPS Cost: 1,640 Bytes: 474 Cardinality: 3
9 MERGE JOIN CARTESIAN Cost: 8 Bytes: 1,530 Cardinality: 17
5 NESTED LOOPS
3 NESTED LOOPS Cost: 4 Bytes: 116 Cardinality: 2
1 INDEX RANGE SCAN INDEX SCHEMA_NAME.IDX_STATUS_T_ID Cost: 2 Bytes: 26 Cardinality: 1
2 INDEX RANGE SCAN INDEX SCHEMA_NAME.IDX_L_DOM_ID Cost: 1 Cardinality: 8
4 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA_NAME.TABLE3 Cost: 2 Bytes: 224 Cardinality: 7
8 BUFFER SORT Cost: 6 Bytes: 224 Cardinality: 7
7 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA_NAME.TABLE3 Cost: 2 Bytes: 224 Cardinality: 7
6 INDEX RANGE SCAN INDEX SCHEMA_NAME.IDX_L_DOM_ID Cost: 1 Cardinality: 8
11 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA_NAME.TABLE1 Cost: 716 Bytes: 68 Cardinality: 1
10 INDEX RANGE SCAN INDEX SCHEMA_NAME.IDX_CI_CD Cost: 57 Cardinality: 11,853
14 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA_NAME.TABLE4 Cost: 1 Bytes: 14 Cardinality: 1
13 INDEX UNIQUE SCAN INDEX (UNIQUE) SCHEMA_NAME.IDX_PROC_ID Cost: 0 Cardinality: 1
16 INDEX UNIQUE SCAN INDEX (UNIQUE) SCHEMA_NAME.PROC_ID Cost: 0 Cardinality: 1
18 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA_NAME.TABLE4 Cost: 1 Bytes: 14 Cardinality: 1