title: Can't Oracle infer these queries are identical - very different execution plan generated
body: <p>I have the following two very similar statements with a subquery. I highlighted the difference with **.</p>
  
  <p>1:</p>
  
  <pre><code>SELECT DISTINCT name
  FROM person, nameindex n
  WHERE person.id1    ='0812'
  AND person.id2    =n.id2
  AND person.id1    =n.id1
  AND n.phonetic IN
    (SELECT n2.phonetic
    FROM nameindex n2
    WHERE n2.id1=person.id1 **
    GROUP BY n2.phonetic
   HAVING COUNT(*) BETWEEN 4 AND 500)
  </code></pre>
  
  <p>2:</p>
  
  <pre><code>SELECT DISTINCT name
  FROM person, nameindex n
  WHERE person.id1    ='0812'
  AND person.id2    =n.id2
  AND person.id1    =n.id1
  AND n.phonetic IN
    (SELECT n2.phonetic
    FROM nameindex n2
    WHERE n2.id1='0812'  **
    GROUP BY n2.phonetic
   HAVING COUNT(*) BETWEEN 4 AND 500)
  </code></pre>
  
  <p>I would think oracle could infer, that <code>person.id1</code> must be constant <code>0812</code> in the subquery. However, both queries produce extremely different execution plans and costs (1: cost 4404211855 while 2: cost: 36237). Why is this?</p>
  
  <p>This is more an analytical query, not an OLTP one, so there are no indices defined for this specific query.</p>
  
  <p>(Background of the query: Get names of persons within id1='0812' who have a phonetic entry in the nameindex table for which there are between 4 and 500 occurrences.)</p>