We looked at the with statement with embedded PL/SQL, now lets look at another use of the with statement. This time we are going to use it with embedded subqueries.
Query A This query searches a stocks table, returns the median closing price for a quarter, the standard deviation and plus and minus one standard deviation.
The top two arrows point to the subquerys and the bottom arrow points to referencing the subqueries. The subqueries are named SDEV and MED, those can then be referenced in the from clause.
Query B shows the normal subquery we are accustom to seeing and returns the same values as Query A.
Query A and Query B are equivalent.
So, what execution plan did the CBO come up with for Query A
And what plan did the CBO come up with for Query B
I’ll dig into how the CBO came up with these execution plans later.