SQL Query Performance

[karmst]

Hi all,

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

-ka

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

[simflex]

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.