Subquery Factoring (5)

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 List of Bug Fixes for 11.2.0.3 (MOS licence required for link) quite recently.

Over the last three or four years I’ve made several comments about 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 as the optimizer did its job. 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):

rem
rem     Script:         with_restriction.sql
rem     Author:         Jonathan Lewis
rem     Dated:          April 2007
rem

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  -- > comment to avoid WordPress format issue
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'EMP',
                cascade          => true,
                estimate_percent => null,
                granularity      => 'DEFAULT',
                method_opt       => 'for all columns size 1'
        );
end;
/

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
;

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 used to get a good execution plan by accident because of the bugs and will now get a bad execution plan because the bug has been fixed.

Maxim: when upgrading (to 11.2.0.3 or higher) 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.