title: Adding redudant join conditions in Oracle results in a different plan
body: <p>I have a common database joining situation involving three tables. One table, A, is the main table with a primary key named <code>id</code>. Tables B and C contain auxiliary data for entries and A, and each also has a column named <code>id</code> which is a foreign key pointing to A.<code>id</code>. Now, if I want all data from A, B and C in one query, I would write:</p>
  
  <pre><code>SELECT *
  FROM A
  INNER JOIN B
  ON B.id = A.id
  INNER JOIN C
  ON C.id = A.id
  </code></pre>
  
  <p>which of course works perfectly.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT *
  FROM A
  INNER JOIN B
  ON B.id = A.id
  INNER JOIN C
  ON C.id = A.id AND C.id = B.id
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>