-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|