title: oracle - same query but different plan in 11g and 12c
body: <p>This Question is relative to <a href="https://stackoverflow.com/questions/34609799/oracle-subquery-in-keyword-is-slow-on-12c">this question</a>.
  This is code i try to use in 12c</p>
  
  <pre><code>  SELECT * FROM DMProgDate_00001
    WHERE 1=1
    AND ProgressOID IN ( 
      SELECT P.OID FROM (
        SELECT OID FROM (
          SELECT A.OID, ROWNUM as seqNum FROM (
            SELECT OID FROM DMProgress_00001 
              WHERE 1=1
              AND Project = 'Moho'
              AND Phase = 'Procurement'
              AND Displine = 'Q340'
              ORDER BY actCode
            ) A
            WHERE ROWNUM &lt;= 20
        ) WHERE seqNum &gt; 0
      ) P
    );
  </code></pre>
  
  <ul>
  <li><p>result</p>
  
  <p>11g : under 1 sec</p>
  
  <p>12c : over 8 sec</p></li>
  </ul>
  
  <p>This is the query plan in 11g
  <a href="https://i.stack.imgur.com/via5E.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/via5E.png" alt="enter image description here"></a></p>
  
  <p>This is the query plan in 12c
  <a href="https://i.stack.imgur.com/amQHj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/amQHj.png" alt="enter image description here"></a></p>
  
  <p>When I take out all pagination code (like below). query in 12c is fast enough as 11g BUT need pagination query. </p>
  
  <pre><code>  SELECT  * FROM DMProgDate_00001
    WHERE 1=1
    AND ProgressOID IN ( 
      SELECT P.OID FROM (
            SELECT OID FROM DMProgress_00001 
              WHERE 1=1
              AND Project = 'Moho'
              AND Phase = 'Procurement'
              AND Displine = 'Q340'
              ORDER BY actCode
      ) P
    );
  </code></pre>
  
  <p>This is the query (without pagination) plan in 12c
  <a href="https://i.stack.imgur.com/tJGYH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tJGYH.png" alt="enter image description here"></a></p>
  
  <p>I tried OFFSET .. key word ( only 12c support) and optimizer_features_enable('11.2.0.4') but same result as above ( over 8 sec).</p>
  
  <p>We need to support both 11g and 12c and I know some detour to fix this problem ( in  <a href="https://stackoverflow.com/questions/34609799/oracle-subquery-in-keyword-is-slow-on-12c">my pre-question</a>) BUT don't want to keep it as same query code. Is there any option or setting that can fix this qeustion?</p>
  
  <hr>
  
  <p>added query plan as text
  (they are different table name but are same table structure and contents)</p>
  
  <pre><code>12c - over 3 sec
  Plan hash value: 3742986389
  
  -----------------------------------------------------------------------------------------
  | Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT     |                  |     1 |   153 |   204   (0)| 00:00:01 |
  |*  1 |  FILTER              |                  |       |       |            |          |
  |   2 |   TABLE ACCESS FULL  | DMPROGDATE_00001 |     1 |   153 |   102   (0)| 00:00:01 |
  |*  3 |   FILTER             |                  |       |       |            |          |
  |*  4 |    COUNT STOPKEY     |                  |       |       |            |          |
  |*  5 |     TABLE ACCESS FULL| DMPROGRESS_00001 |    26 |  2288 |   102   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     1 - filter( EXISTS (&lt;not feasible&gt;)
     3 - filter("OID"=:B1)
     4 - filter(ROWNUM&lt;=20)
     5 - filter("PROJECT"='Moho' AND "PHASE"='Procurement' AND "DISPLINE"='Q340')
  
  Note
     - dynamic statistics used: dynamic sampling (level=2)
     - 1 Sql Plan Directive used for this statement
  
  
  
  
  
  11g - 0.01 sec
  
  Plan hash value: 833434956
  -----------------------------------------------------------------------------------------
  | Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT     |                  |    13 |  1157 |    57   (2)| 00:00:01 |
  |*  1 |  HASH JOIN RIGHT SEMI|                  |    13 |  1157 |    57   (2)| 00:00:01 |
  |   2 |   VIEW               | VW_NSO_1         |     3 |    81 |    34   (0)| 00:00:01 |
  |*  3 |    COUNT STOPKEY     |                  |       |       |            |          |
  |*  4 |     TABLE ACCESS FULL| DMPROGRESS_00037 |     3 |    99 |    34   (0)| 00:00:01 |
  |   5 |   TABLE ACCESS FULL  | DMPROGDATE_00037 |  7388 |   447K|    22   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - access("PROGRESSOID"="OID")
     3 - filter(ROWNUM&lt;=20)
     4 - filter("DISPLINE"='Q340' AND "PHASE"='Procurement' AND "PROJECT"='Moho')
  </code></pre>