I have a query that takes 1.32 seconds to be executed and I want to optimize it. Here is the query:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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?
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.