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?
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