Subquery Factoring (3)

From time to time I’ve warned people that subquery factoring should be used with a little care if all you’re trying to do is make a query more readable by extracting parts of the SQL into “factored subqueries” (or Common Table Expressions – CTEs – if you want to use the ANSI term for them). In principle, for example, the following two queries should produce the same  execution plan:

select
        t1.*
from    t1,
        (
         select
                distinct n30
         from   t2
         where
                n30 < 5  -- > comment to avoid wordpress format issue
        ) subq
where
        t1.n30 = subq.n30
;

with subq as (
        select
                distinct n30
        from   t2
        where
                n30 < 5  -- > comment to avoid wordpress format issue
)
select
        t1.*
from
        t1,
        subq
where
        t1.n30 = subq.n30
;

All I’ve done in the second query is changed the inline view to a common table expression at the top of the statement in the hope of making the query text look simpler. When I do this, I expect Oracle to copy the CTE back in line and then optimize, coming up with exactly the same plan in both cases. Unfortunately this is what I find:

For the query with the inline view, I see that the optimizer has applied complex view merging, joining early and aggregating late:

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   161 | 14007 |   302   (2)| 00:00:04 |
|   1 |  VIEW                |      |   161 | 14007 |   302   (2)| 00:00:04 |
|   2 |   HASH UNIQUE        |      |   161 | 21252 |   302   (2)| 00:00:04 |
|*  3 |    HASH JOIN         |      |   161 | 21252 |   301   (1)| 00:00:04 |
|*  4 |     TABLE ACCESS FULL| T2   |   150 |  1800 |   150   (1)| 00:00:02 |
|*  5 |     TABLE ACCESS FULL| T1   |   150 | 18000 |   150   (1)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."N30"="N30")
   4 - filter("N30"<5)
   5 - filter("T1"."N30"<5)

For the query with the CTE, the optimizer has not used complex view merging, it’s aggregated early and joined late.

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   150 | 20250 |   302   (2)| 00:00:04 |
|*  1 |  HASH JOIN           |      |   150 | 20250 |   302   (2)| 00:00:04 |
|   2 |   VIEW               |      |     5 |    75 |   151   (2)| 00:00:02 |
|   3 |    HASH UNIQUE       |      |     5 |    40 |   151   (2)| 00:00:02 |
|*  4 |     TABLE ACCESS FULL| T2   |   150 |  1200 |   150   (1)| 00:00:02 |
|   5 |   TABLE ACCESS FULL  | T1   | 30000 |  3515K|   150   (1)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."N30"="SUBQ"."N30")
   4 - filter("N30"<5)

This particular test case behaves the same way in 10.2.0.3 and 11.1.0.6. Since I’m running with CPU costing (system statistics), it’s possible that you won’t be able to reproduce the same result without some fiddling with data sizes or database parameters, but here’s the code to generate the two tables:

rem
rem     Script:         subq_difference.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2010
rem
 
create table t1
as
with generator as (
        select  --+ materialize
                rownum  id
        from    all_objects
        where   rownum <= 3000  -- > comment to avoid wordpress format issue
)
select
        rownum                  id,
        mod(rownum,1000)        n30,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        generator       v1,
        generator       v2
where
        rownum <= 30000
;

create table t2
as
with generator as (
        select  --+ materialize
                rownum  id
        from    all_objects
        where   rownum <= 3000  -- > comment to avoid wordpress format issue
)
select
        rownum                  id,
        mod(rownum,1000)        n30,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        generator       v1,
        generator       v2
where
        rownum <= 30000  -- > comment to avoid wordpress format issue
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                estimate_percent => null,
                block_sample     => true,
                method_opt       => 'for all columns size 1',
                cascade          => true
        );

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

I also set the system statistics with the following PL/SQL:

begin
	dbms_stats.set_system_stats('MBRC',8);
	dbms_stats.set_system_stats('MREADTIM',26);
	dbms_stats.set_system_stats('SREADTIM',12);
	dbms_stats.set_system_stats('CPUSPEED',800);
end;

You might want to fiddle around with newer releases and see if this result is reproducible – it’s always possible that it’s fixed in 10.2.0.4 and 11.1.0.7, but there are other bugs relating to CTEs and the CBO choosing different optimization strategies because of changes in the path through the code when it sees a CTE.

Update 7th Aug 2013

Fixed by 11.2.0.3 – view merging took place in both cases on the system I ran it.

On 12c I got the same plan for both versions of the query – but on that system it was the non-merged view that appeared in both cases.