I have the following query that uses an IN clause, specifically for the 'SUBMITTAL.FACILITY_ID IN (select facility_id from vu_vendor where vendor_id in (1010))' part. Is there any way I can replace the IN clause to make the SQL Query use INDEXES and perform faster?
Thanks in advance
select STATUS.STATUS_TXT, count(*) count, sum(equipment_total_cost) totalCost
FROM ODS_SUBMITTAL_STATUS STATUS, ODS_SUBMITTAL SUBMITTAL
WHERE (SUBMITTAL.submittal_id, STATUS.CREATED_DATE)
IN (SELECT submittal_id, MAX(created_date) FROM ods_submittal_status GROUP BY submittal_id)
AND (SUBMITTAL.submittal_id, SUBMITTAL.version_nbr)
IN (SELECT submittal_id, MAX(version_nbr) FROM ods_submittal GROUP BY submittal_id)
AND (SUBMITTAL.SUBMITTAL_ID = STATUS.SUBMITTAL_ID)
AND SUBMITTAL.OWNER_USERNAME = 'username'
AND ( SUBMITTAL.FACILITY_ID IN (select facility_id from vu_vendor where vendor_id in (1010)) )
GROUP BY STATUS.STATUS_TXT
I will try WHERE EXISTS.
The WHERE EXISTS clause works more like an indexed column because your search ends the instance a hit is made.
The IN clause as you correctly stated uses full table scan because it ensures that the entire table is searched.
Click Here to Expand Forum to Full Width