I have a query that takes 1.32 seconds to be executed and I want to optimize it. Here is the query:
SELECT E.DEPT_CODE, (SUM( SESSION_SCHEL )) AS AMOUNT FROM HISTORY H, SESSIONS S, EMPLOYEE E, DEPARTMENT D WHERE H.SESSION_CODE = S.SESSION_CODE AND H.EMP_ID = E.EMP_ID AND E.DEPT_CODE = D.DEPT_CODE AND S.SESSION_TYPE='D' AND H.REQ_FLAG='Y' AND TRUNC(H.SESSION_DATE) >= TO_DATE('30-11-2004', 'DD/MM/RRRR') AND TRUNC(H.SESSION_DATE) <= TO_DATE('24-04-2006', 'DD/MM/RRRR') AND D.AREA_CODE = '18' GROUP BY E.DEPT_CODE
The sessions table contains 1,686,862 records. The history table contains 4,110,637 records. The Employee table contains 76,919 records. The Department table contains 19 records.
Here is my execution plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Hint=CHOOSE 1 5473 SORT GROUP BY 1 48 5473 NESTED LOOPS 1 48 5471 MERGE JOIN CARTESIAN 1 35 5470 HASH JOIN 1 29 5469 INDEX FAST FULL SCAN PFR_HISTORY_EMP 2 K 50 K 3318 TABLE ACCESS FULL SESSIONS 843 K 7 M 2070 BUFFER SORT 1 6 2152 TABLE ACCESS BY INDEX ROWID DEPARTMENT 1 6 1 INDEX RANGE SCAN XIF64DEPARTMENT 1 TABLE ACCESS BY INDEX ROWID EMPLOYEE 169 K 2 M 1 INDEX UNIQUE SCAN INDX_EMP_ID 1
Maybe this statement could be rewritten for better performance. Could you please help with that?
This was first published in June 2006