Q

Help with optimizing a query

I have a query that takes 1.32 seconds to be executed and I want to optimize it. 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. Maybe this statement could be rewritten for better performance. Could you please help with that?

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?

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

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close