5

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.

3
  • What is the cardinality of this query: select TaskId from ObjectAffectingTasks where ObjectId=12345 Maybe Oracle thinks that there are lot of TaskId'es that suit the desired ObjectId so it is easier to make full table scan that to make many index accesses. In case Oracle make a wrong decision about the cardinality, maybe update the statistics to help him make a correct one.
    – jutky
    Jun 5, 2014 at 11:59
  • Currently, the Tasks table has more than 300000 rows, ObjectAffectingTasks only 20 (to become more), where tasks were started on parent/container objects. Finally, the direct/indirect task ratio (obejctID in Tasks vs AffectingTasks table) may be 3 to 1. The number of tasks per object ID is usually 5 to 20. However, the number of rows in Tasks and also the left joined tables is quite huge.
    – Erik Hart
    Jun 5, 2014 at 12:12
  • 1
    I finally managed to implement the above Union-Subselect syntax, since it was a very specific part of the code. Oracle now uses all my indices and has decent performance. Even SQL Server performs notably better (25%:75% showing exe plans for both queries), although it never fell back to the full-table-scan disaster seen in Oracle. I wonder if the databases can't do any better, or if it still was a statistics problem.
    – Erik Hart
    Jun 13, 2014 at 16:21

1 Answer 1

2

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:

...
where t.ObjectId = 12345
or t.Id in (
    select /*+ PRECOMPUTE_SUBQUERY */ TaskId 
    from ObjectAffectingTasks where ObjectId=12345
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.