Q

Subquery slows query performance

Here is a simplified version of the actual query:

 
SELECT  M.CNTR_NO  
FROM TB_GATE_TRAN GT,TB_MASTER M 
WHERE  GT.VSL_CD = M.VSL_CD AND  GT.CNTR_NO
 = M.CNTR_NO  AND GT.CNTR_NO NOT IN  
   (SELECT CNTR_NO FROM VT_APPT  
    WHERE APPT_MOVE_TYPE = 'EmptyIn' AND STATUS IN ('Act','Mis')
    AND  TRUCK_CO IN ('QTPI') 
    AND  APPT_MAKE_TIME > (trunc(SYSDATE) - 104)
    );
Both the main and subquery have been simplified. I'm using Oracle 9i.

Rows in TB_MASTER: 162000
in TB_GATE_TRAN: 113000
in VT_APPT: 109000

There are indexes on VT_APPT:
 
VT_APPT(APPT_MOVE_TYPE)
VT_APPT(STATUS)
VT_APPT(APPT_MOVE_TYPE,status)
VT_APPT(CNTR_NO)
For the data that we are using, the subquery does not return any data, as there is no qualifying data for the new project. Also there may be CNTR_NO in VT_APPT which are not present in TB_MASTER as well as TB_GATE_TRAN.

The problem: With the subquery, the SQL takes a lot of time, about 190 secs to return 520 rows. If we remove the subquery, it returns in 1 or 2 seconds. How can we speed up the query?

Your best bet is to cast the query in different (but semantically equivalent) forms. For example, you could try using
 SELECT  M.CNTR_NO  FROM
TB_GATE_TRAN GT,TB_MASTER M
 WHERE  GT.VSL_CD = M.VSL_CD 
AND  GT.CNTR_NO = M.CNTR_NO  
AND NOT EXISTS  
  (SELECT 1 FROM
  VT_APPT  VT WHERE VT.CNTR_NO = GT.CNTR_NO
  AND VT.APPT_MOVE_TYPE = 'EmptyIn' AND VT.STATUS IN ('Act','Mis')
  AND  VT.TRUCK_CO IN ('QTPI') 
  AND  VT.APPT_MAKE_TIME > (trunc(SYSDATE) - 104));
You could also use an antijoin of VT_APPT and TB_GATE_TRAN (where VT.CNTR_NO != GT.CNTR_NO), or create an inline view of your subquery. The point is to try the various ways that SQL allows you to express a query. You will frequently find that one approach is orders of magnitude faster than others.
This was first published in May 2005

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close