I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans:
select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 columns] , od2...[ObjectData2 columns] from Tasks t left join ObjectData1 od1 on t.ObjectId=od1.ObjectId left join ObjectData2 od2 ... ... [more leftjoins] ... where t.ObjectId = 12345 or t.Id in ( select TaskId from ObjectAffectingTasks where ObjectId=12345 )
Tasks has indices on Id, ObjectId; ObjectAffectingTasks has index on both ObjectId and TaskId. All joined tables have proper indices, too. The ObjectAffectingTasks table contains task IDs affecting the object, but having another in the ObjectID, so all Tasks affecting object with ID 12345 shall be selected.
When analyzing the query, it seemed to be the OR condition that spoiled the execution plan. Where clauses with only ObjectId or only the subquery were using all the indices. Another workaround was to create a Union subquery, which also used the indices:
where t.Id in ( select TaskId from ObjectAffectingTasks where ObjectId=12345 union select Id from Tasks where ObjectId = 12345 )
I would, anyway, expect the original query and IN subquery to be translated into a semi-join with index lookup on both Id and ObjectId. However, I see the IN subquery is internally translated into EXISTS (select 0 from...)
May this be only a statistics problem, or does the OR condition spoil it all? Is it possible to lookup the indices on Tasks and ObjectAffectingTasks with such an OR condition?
Unfortunately, the queries are created by rather complex program code, so rewriting them would be way too much work. Another issue is that they're a bit generic, being used for both Oracle and MS SQL Server, so proprietary hints for index usage are not an option, either.