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.
Best Answer
I had a very similar issue in Oracle 12c. In experimenting, the PRECOMPUTE_SUBQUERY optimizer hint 'forced' the subquery to execute first, thus making use of the defined indexes. In your snippet, that would look like this: