Help with optimizing a query

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?

    Requires Free Membership to View

    Login

I think a second and a half for a query against two multi-million-row tables is pretty good. But you can always experiment with optimizer hints and generating histograms on columns. Decide how fast you want the query to execute and then work toward that goal, but understand that you have limited room for improvement.

This was first published in June 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top