Here is a simplified version of the actual query:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.