Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: