Subquery Factoring

It’s always worth browsing through the list of Oracle’s bug fixes each time a new release or patch comes out because it can give you clues about where to look for problems in your production release – and how to anticipate problems on the upgrade. This article is an example of a fix that I found while while looking at the note for 11.2.0.3 (MOS licence required for link) quite recently.

Over the last three or four years I’ve made several commentsabout how subquery factoring could result in changes in execution plans even if the “factored subquery” (or “common table expression” (CTE) to use the more appropriate technical term) was moved in line. In the 11.2.0.3 bug fixes list, though, you’ll find the following bug fixes:

Bug 11740670 Different execution plans with and without subquery factoring (WITH clause)
Bug 9002661 Transitive predicate were not generated in WITH clause.

So here’s an example that’s been sitting on my laptop for a few years, waiting for the right bug fix (it’s using my standard setup of 8KB block size, LMT with 1MB uniform extents, but not ASSM, and CPU costing disabled):

create table emp (
	dept_no		not null,
	sal,
	emp_no,
	padding,
	constraint e_pk primary key(emp_no)
)
as
select
	mod(rownum,6),
	rownum,
	rownum,
	rpad('x',60)
from
	all_objects
where
	rownum <= 20000
;

-- collect stats, compute, no histograms

select
	outer.*
from
	emp outer
where
	sal > 1000000
and	outer.sal > (
		select
			avg(inner.sal)
 		from	emp inner
		where	inner.dept_no = outer.dept_no
	)
;

with subq as (
	select
		outer.*
	from
		emp outer
	where
		sal > 1000000
	and	outer.sal > (
			select
				avg(inner.sal)
 			from	emp inner
			where	inner.dept_no = outer.dept_no
		)
	)
select	*
from	subq
;

-- reference script: with_restriction.sql

In 11.1.0.7 (and earlier) the first version of this query uses a simple filter subquery, and the second version inlines the factored subquery (which should, therefore, produce the same plan) but then unnests the subquery and uses “group by placement” to resolve the plan as a hash join followed by hash aggregation. This gives us two completely different plans:

Plan for first form of the query
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    72 |    70 |
|*  1 |  FILTER             |      |       |       |       |
|*  2 |   TABLE ACCESS FULL | EMP  |     1 |    72 |    35 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    35 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM "EMP"
              "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   2 - filter("SAL">1000000)
   4 - filter("INNER"."DEPT_NO"=:B1)

Plan with factored subquery moved in line
-------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    83 |    93 |
|*  1 |  FILTER              |      |       |       |       |
|   2 |   HASH GROUP BY      |      |     1 |    83 |    93 |
|*  3 |    HASH JOIN         |      |  1667 |   135K|    71 |
|*  4 |     TABLE ACCESS FULL| EMP  |     1 |    72 |    35 |
|   5 |     TABLE ACCESS FULL| EMP  | 20000 |   214K|    35 |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">AVG("INNER"."SAL"))
   3 - access("INNER"."DEPT_NO"="OUTER"."DEPT_NO")
   4 - filter("SAL">1000000)

Under 11.2.0.3, however, the first plan appears for both forms of the query.

On the plus side – this is good, because Oracle is now behaving consistently. On the minus side – this is bad, because someone, somewhere, is going to be the unlucky person who managed to get a good execution plan by accident because of the bugs.

Maxim: when upgrading to 11.2.0.3, keep a close eye on any queries you’ve got that include subquery factoring – in fact, you might decide to capture SQL Baselines (without activating them) for every single one of them before doing the upgrade so that you can enforce the pre-upgrade plan if the post-upgrade plan is less efficient.

Footnote: I haven’t finished with this example – there’s an interesting follow-up that appeared in the remainder of my test code when I hinted the subquery into materialization. But that’s a story for another day.

[Further reading on "subquery factoring"]