title: Oracle query slow when joining, fast when splitted into two queries
body: <p>I have a sql query which is quite slow when joining two tables but is fast when I first query one table and with its output query the other table.</p>
  
  <p><strong>Environment/Preconditions</strong></p>
  
  <p>Oracle Version:<br>
  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production</p>
  
  <p>Objects involved:<br>
  cfc_materialized (materialized view, 21,5 Mio rows)<br>
  contact (table, 12,6 Mio rows)</p>
  
  <p>Indexes involved:<br>
  CREATE INDEX CONTACT_CLIENT ON CONTACT(CLIENT);<br>
  CREATE INDEX CFC_MATERIALIZED_A_S_T ON CFC_MATERIALIZED(ASSOCIATION_TYPE, SOURCEID, TARGETID); </p>
  
  <p>I have already recalculated the statistics of the 2 tables with cascade => true:</p>
  
  <pre><code>BEGIN
    SYS.DBMS_STATS.GATHER_TABLE_STATS (
       OwnName        =&gt; '...'
      ,TabName        =&gt; '...'
      ,Estimate_Percent  =&gt; 0
      ,Degree            =&gt; 4
      ,Cascade           =&gt; TRUE
      ,No_Invalidate     =&gt; FALSE);
  END;
  /
  </code></pre>
  
  <p><strong>Problem</strong></p>
  
  <p>I have the following query:</p>
  
  <pre><code>SELECT c.*
  FROM contact c
  WHERE c.client in (
      SELECT cfc.targetid
      FROM cfc_materialized cfc
      WHERE cfc.sourceid = 'e95027f0-a83e-11e3-a0ae-005056aebabc'
        AND cfc.association_type = 'ContactDataSharing'
    )
    AND c.deleted = 0;
  </code></pre>
  
  <p>Explain plan:</p>
  
  <pre><code>--------------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
  --------------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |                  |    11M|  2214M| 38976   (1)| 00:07:48 |        |      |            |
  |   1 |  PX COORDINATOR                |                  |       |       |            |          |        |      |            |
  |   2 |   PX SEND QC (RANDOM)          | :TQ10001         |       |       |            |          |  Q1,01 | P-&gt;S | QC (RAND)  |
  |   3 |    NESTED LOOPS                |                  |       |       |            |          |  Q1,01 | PCWP |            |
  |   4 |     NESTED LOOPS               |                  |    11M|  2214M| 38976   (1)| 00:07:48 |  Q1,01 | PCWP |            |
  |   5 |      SORT UNIQUE               |                  |  2164 |   196K| 10672   (1)| 00:02:09 |  Q1,01 | PCWP |            |
  |   6 |       PX RECEIVE               |                  |  2164 |   196K| 10672   (1)| 00:02:09 |  Q1,01 | PCWP |            |
  |   7 |        PX SEND HASH            | :TQ10000         |  2164 |   196K| 10672   (1)| 00:02:09 |  Q1,00 | P-&gt;P | HASH       |
  |   8 |         PX BLOCK ITERATOR      |                  |  2164 |   196K| 10672   (1)| 00:02:09 |  Q1,00 | PCWC |            |
  |*  9 |          MAT_VIEW ACCESS FULL  | CFC_MATERIALIZED |  2164 |   196K| 10672   (1)| 00:02:09 |  Q1,00 | PCWP |            |
  |* 10 |      INDEX RANGE SCAN          | CONTACT_CLIENT   |  5500 |       |    37   (0)| 00:00:01 |  Q1,01 | PCWP |            |
  |* 11 |     TABLE ACCESS BY INDEX ROWID| CONTACT          |  5474 |   550K|   973   (0)| 00:00:12 |  Q1,01 | PCWP |            |
  --------------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     9 - filter("CFC"."SOURCEID"='e95027f0-a83e-11e3-a0ae-005056aebabc' AND "CFC"."ASSOCIATION_TYPE"='ContactDataSharing')
    10 - access("C"."CLIENT"="CFC"."TARGETID")
    11 - filter("C"."DELETED"=0)enter code here
  </code></pre>
  
  <p>This takes quite long and I am wondering why there is a full access on cfc_materialized.
  When I split the query into 2 queries, it is much more faster.
  1st query:</p>
  
  <pre><code>SELECT cfc.targetid
  FROM cfc_materialized cfc
  WHERE cfc.sourceid = 'e95027f0-a83e-11e3-a0ae-005056aebabc'
    AND cfc.association_type = 'ContactDataSharing';
  </code></pre>
  
  <p>This query returns 2 targetids.
  Explain plan:</p>
  
  <pre><code>-------------------------------------------------------------------------------------------
  | Id  | Operation        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT |                        |  2164 |   196K|    36   (0)| 00:00:01 |
  |*  1 |  INDEX RANGE SCAN| CFC_MATERIALIZED_A_S_T |  2164 |   196K|    36   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - access("CFC"."ASSOCIATION_TYPE"='ContactDataSharing' AND 
                "CFC"."SOURCEID"='e95027f0-a83e-11e3-a0ae-005056aebabc')
  </code></pre>
  
  <p>Here the 2nd query. I use the output of the 1st query as input for the IN-parameter:</p>
  
  <pre><code>SELECT *
  FROM contact c
  WHERE c.client in (
      'e95027f0-a83e-11e3-a0ae-005056aebabc',
      'eb37d3b0-a83e-11e3-a0ae-005056aebabc'
    )
    AND c.deleted = 0;
  </code></pre>
  
  <p>Explain plan:</p>
  
  <pre><code>-----------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                |  2814 |   283K|   505   (0)| 00:00:07 |
  |   1 |  INLIST ITERATOR             |                |       |       |            |          |
  |*  2 |   TABLE ACCESS BY INDEX ROWID| CONTACT        |  2814 |   283K|   505   (0)| 00:00:07 |
  |*  3 |    INDEX RANGE SCAN          | CONTACT_CLIENT |  2827 |       |    23   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter("C"."DELETED"=0)
     3 - access("C"."CLIENT"='e95027f0-a83e-11e3-a0ae-005056aebabc' OR 
                "C"."CLIENT"='eb37d3b0-a83e-11e3-a0ae-005056aebabc')
  </code></pre>
  
  <p><strong>Question</strong></p>
  
  <p>So my question is, why is the CBO not executing the query in a similar way like doing it in 2 queries manually? I also tried the query with hints to use the same indexes like in the 2 queries:</p>
  
  <pre><code>SELECT /*+ index(c CONTACT_CLIENT) */ c.*
  FROM contact c
  WHERE c.client in (
      SELECT /*+ index(cfc CFC_MATERIALIZED_A_S_T) */ cfc.targetid
      FROM cfc_materialized cfc
      WHERE cfc.sourceid = 'e95027f0-a83e-11e3-a0ae-005056aebabc'
        AND cfc.association_type = 'ContactDataSharing'
    )
    AND c.deleted = 0;
  </code></pre>
  
  <p>But then I get an even worse explain plan:</p>
  
  <pre><code>-------------------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                        |    11M|  2214M|   305K  (1)| 01:01:09 |
  |   1 |  NESTED LOOPS                |                        |       |       |            |          |
  |   2 |   NESTED LOOPS               |                        |    11M|  2214M|   305K  (1)| 01:01:09 |
  |   3 |    SORT UNIQUE               |                        |  2164 |   196K|    36   (0)| 00:00:01 |
  |*  4 |     INDEX RANGE SCAN         | CFC_MATERIALIZED_A_S_T |  2164 |   196K|    36   (0)| 00:00:01 |
  |*  5 |    INDEX RANGE SCAN          | CONTACT_CLIENT         |  5500 |       |    37   (0)| 00:00:01 |
  |*  6 |   TABLE ACCESS BY INDEX ROWID| CONTACT                |  5474 |   550K|   973   (0)| 00:00:12 |
  -------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     4 - access("CFC"."ASSOCIATION_TYPE"='ContactDataSharing' AND 
                "CFC"."SOURCEID"='e95027f0-a83e-11e3-a0ae-005056aebabc')
     5 - access("C"."CLIENT"="CFC"."TARGETID")
     6 - filter("C"."DELETED"=0)
  </code></pre>
  
  <p>I also tried to do a join instead of an IN clause like suggested by Alexander and StanislavL:</p>
  
  <pre><code>SELECT c.*
  FROM contact c
  JOIN cfc_materialized cfc ON c.client = cfc.targetid
  WHERE cfc.sourceid = 'e95027f0-a83e-11e3-a0ae-005056aebabc'
    AND cfc.association_type = 'ContactDataSharing'
    AND c.deleted = 0;
  </code></pre>
  
  <p>and got the following explain plan, which is again quite slow (slower than the two separate queries):</p>
  
  <pre><code>---------------------------------------------------------------------------------------------
  | Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |                        |    11M|  2214M| 51745   (1)| 00:10:21 |
  |*  1 |  HASH JOIN         |                        |    11M|  2214M| 51745   (1)| 00:10:21 |
  |*  2 |   INDEX RANGE SCAN | CFC_MATERIALIZED_A_S_T |  2164 |   196K|    36   (0)| 00:00:01 |
  |*  3 |   TABLE ACCESS FULL| CONTACT                |    12M|  1237M| 51649   (1)| 00:10:20 |
  ---------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - access("C"."CLIENT"="CFC"."TARGETID")
     2 - access("CFC"."ASSOCIATION_TYPE"='ContactDataSharing' AND 
                "CFC"."SOURCEID"='e95027f0-a83e-11e3-a0ae-005056aebabc')
     3 - filter("C"."DELETED"=0)
  </code></pre>