Adding redudant join conditions in Oracle results in a different plan

Question

I have a common database joining situation involving three tables. One table, A, is the main table with a primary key named id. Tables B and C contain auxiliary data for entries and A, and each also has a column named id which is a foreign key pointing to A.id. Now, if I want all data from A, B and C in one query, I would write:

SELECT *
FROM A
INNER JOIN B
ON B.id = A.id
INNER JOIN C
ON C.id = A.id

which of course works perfectly.

Recently, our DBA told us that this is inefficient in Oracle, and you need to join conditions between C and B as well, as follows:

SELECT *
FROM A
INNER JOIN B
ON B.id = A.id
INNER JOIN C
ON C.id = A.id AND C.id = B.id

This looked redundant to me, so naturally I didn't believe here. Until I actually ran into a slow query that had a terrible execution plan, and managed to fix it by exactly adding the missing join condition. I ran explain plan on both versions: the one without the "redundant" query condition had a cost of 1 035 while the "improved" one had 389 (and there were huge differences in cardinality and bytes as well). Both queries produced the exact same result.

Can anyone explain why this extra condition makes a difference? To me C and B are not even related. Note also that if you take away the other join condition it is equally bad - they both need to be there.

Answer 1

Those two queries do not look the same to me at all.
Then again I am not the Oracle optimizer.

Since both B and C have foreign keys of A when you have done this

INNER JOIN B
ON B.id = A.id

the resultant resultset (say that a few times fast) on which you join table C in your second query is smaller, as you saw in your execution-plan results, than it is when you join table C solely to table A as in your first query.

So you've optimized things in the second query by joining table C on the smaller set of data that is the intersection of A and B, and the smaller set of data that is the intersection of A and C.

Answer 2

Oracle's optimizer doesn't make transitive assumptions about equality. Although we understand that if A = B and A = C, then B = C, Oracle doesn't assume there is a relationship between B & C unless there is one explicitly stated in the WHERE clause or JOIN conditions.

I'm assuming you have other constraints on A, B and/or C (as opposed to just selecting the entire contents of the tables - otherwise your I/O wouldn't be so low unless your tables were tiny, at which point optimization is somewhat moot). So there are really more constraints on A, B & C than what you specify. Oracle's optimizer will look at all tables in the FROM clause, list the constraints against them in the WHERE clause, and then determine the selectivity of the constraints based on the indexes for those tables. It will then go through various permutations of attack plans and determine which yields the most hope (those are the cardinality values you see in the plans). Without the B = C condition, it will exclude plans that start with B and proceed to C (or vice versa), and those may be the best plans possible.

Answer 3

What you've got is two issues.

Firstly, with the original SQL, the optimizer makes an estimate about the number of rows in A with rows matching the ID in B which also have a matching row in C. The estimate is inaccurate, and the wrong plan is chosen.

Now, you add the redundant condition. Oracle assumes no conditions are truly redundant (as, if they were, an intelligent developer wouldn't include them). It also assumes that each condition is independent of the others. For example, a select where hair = 'bald' may get 10% of a table, a select where gender = 'F' may get 50%. Oracle would assume that a select where hair = 'bald' and gender = 'F' would give 5% (whereas in reality baldness is mostly limited to men).

By adding the 'redundant' predicate, Oracle will over-estimate the numbers or rows to be excluded and will choose the plan accordingly.

If, with the redundant predicate, Oracle is choosing a better plan, it suggests that the estimates for the original query over-estimated the number of rows matching. The redundant predicate is countering that with an under-estimate. And in this case, two wrongs are making a right.

It's not a solution I'd recommend, but if it works.....

PS. I'm assuming data types of all the IDs are consistent. If B.ID and C.ID are date and A.ID was character, or vice versa, then it is possible to have some rows where A.ID = B.ID and A.ID = C.ID but B.ID != C.ID, because implicit conversion may lose timestamps.

Answer 4

Interesting.

It seems that Oracle can infer this transitive equality only under some circumstances : They call it Transitive Closure and you should be able to benefit from it when query rewriting is enabled.

But to be on the safe side, better spell out the redundant predicate yourself.