title: Oracle 12c Subquery Factoring Inline View now has bad plan?
body: <p><strong>Update 11/2</strong></p>
  
  <p>After some additional troubleshooting, my team was able to tie this Oracle bug directly to a parameter change that was made on the <code>12c</code> database the night before the query stopped working. After experiencing some performance issues from an application tied to this database, my team had our DBA change the <code>OPTIMIZER_FEATURES_ENABLE</code> parameter from <code>12.1.02</code> to <code>11.2.0.4</code>. This fixed the performance issue for the problem application but caused the bug I have described above. To verify, I've been able to replicate this same issue in a separate environment by changing this parameter. My DBA has filed a ticket with Oracle to have this looked at. </p>
  
  <p>As a workaround, I was able to make a slight change to my query in order to retrieve the expected results. Specifically, I combined <code>Subquery1</code> with <code>Subquery2</code> and I moved a few predicates in <code>Subquery1</code> from the <code>WHERE</code> clause to the <code>JOIN</code> (where they more properly belonged). This change edited my execution plan (it is slightly less efficient than what was listed before) but was enough to address the original issue. </p>
  
  <hr>
  
  <p><strong>Original Post</strong></p>
  
  <p><em>Firstly, let me apologize for any vagueness in this question but I'm dealing with a confidential financial system so I am forced to hide certain implementation details.</em></p>
  
  <p><strong>Background</strong></p>
  
  <p>I have an <code>Oracle</code> query that I put into production a long time ago that has recently stopped producing expected results coincidentally after an upgrade from <code>11g</code> to <code>12c</code>. To my (and my production support team's) knowledge this query had been working fine for well over a year before that. </p>
  
  <p><strong>Details</strong></p>
  
  <p>The query is overly complicated and not very efficient but this is in large part because I am dealing with non-normalized tables (historically modeled after a Mainframe) and poor data input from upstream systems. In order to deal with a complicated business situation I leveraged multiple levels of Subquery Factoring (the <code>WITH</code> statement) and then my final statement joins together two Inline Views. The basic structure of the query without all of the complicated predicates is as follows:</p>
  
  <p>I have 3 tables <code>Table1</code>, <code>Table2</code>, <code>Table3</code>. <code>Table1</code> is a processing table made up of records from <code>Table2</code>.</p>
  
  <pre><code>--This grabs a subset from Table1
  WITH Subquery1 as (
     SELECT FROM Table1),
  
  --This eliminates certain records from the first subset based on sister records 
  --from the original source table 
  Subquery2 as (
     SELECT FROM Subquery1
     WHERE NOT EXISTS FROM (SELECT from Table2)),
  
  --This ties the records from Subquery2 to Table3
  Subquery3 as (
     SELECT FROM Table3
     JOIN (SELECT Max(Date) FROM Table3)
     JOIN Subquery2)
  
  --This final query evaluates subquery3 in two different ways and 
  --only takes those records which fit the criteria items from both sets
  SELECT FROM 
  (SELECT FROM Subquery3)             -- Call this Inline View A
  JOIN (SELECT FROM Subquery3)        -- Call this Inline View B
  </code></pre>
  
  <p>The <strong>final query</strong> is pretty basic:</p>
  
  <pre><code>   SELECT A.Group_No, B.Sub_Group, B.Key, B.Lob               
     FROM   (SELECT Group_No, Lob, COUNT(Sub_Group) 
             FROM   Subquery3 
             GROUP BY Group_No, Lob
             HAVING COUNT(Sub_Group) = 1) A 
     JOIN (SELECT Group_No, Sub_Group, Key, Lob
           FROM   Subquery3 
           WHERE  Sub_Group LIKE '0000%') B 
     ON A.Group_No = B.Group_No
     AND A.Lob = B.Lob
  </code></pre>
  
  <p><strong>Problem</strong></p>
  
  <p>If I edit the final query to remove the second Inline View and evaluate the output of the <code>A</code> inline view, I come away with <strong>0 returned rows</strong>. I've manually evaluated the records for each individual subquery and can confirm this is an  expected result.</p>
  
  <p>Likewise, if I edit the final query to produce the output of only the 'B' inline view, I come away with <strong>6 returned rows</strong>. Again, I've manually evaluated the data and this is exactly as expected.</p>
  
  <p>Now when joining these two subsets (Inline View <code>A</code> and Inline View <code>B</code>) together, I would expect that the final query result would be 0 rows (since an inner join between a full set and an empty set produces no matches). However, when I run the entire query with the inner join as described above, <strong>I am getting back 1158 rows</strong>!</p>
  
  <p>I have reviewed the Execution Plan but nothing jumps out at me:</p>
  
  <p><a href="https://i.stack.imgur.com/829yO.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/829yO.jpg" alt="Execution Plan 1"></a>
  <a href="https://i.stack.imgur.com/fYaCF.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fYaCF.jpg" alt="Execution Plan 2"></a></p>
  
  <p><strong>Questions</strong></p>
  
  <p>Clearly I have done something to confuse the Oracle Optimizer and the updated query plan is pulling back a much different query than the one I have submitted. My best guess is that with all of these temporary views floating around within the same query, I have confused Oracle into evaluating some set before one that it depends upon. </p>
  
  <p>To this day I've been unable to locate the official Oracle documentation around the <code>WITH</code> statement so I've never been completely confident about the order that subqueries are evaluated. I did notice in searching SO (<em>can't find it now</em>) someone mentioned that a factored subquery cannot refer to another factored query. I've never known this to be true before but the bizarre output above is making me wonder if I had only been lucky before with this query? </p>
  
  <p>Can anyone explain the behavior I am seeing? Am I attempting to do something obviously incorrect with this query plan? Or alternatively, is there any chance that something changed between 11g and 12c that could explain why the behavior of this query might have changed?</p>