Heuristic Temp Table Transformation - 2

Some time ago I've demonstrated the non-cost based decision for applying the temp table transformation when using CTEs (Common Table/Subquery Expressions). In this note I want to highlight another aspect of this behaviour.

Consider the following data creating a table with delibrately wide columns:

create table a
as
  select
          rownum as id
        , rownum as id2
        , rpad('x', 4000) as large_vc1
        , rpad('x', 4000) as large_vc2
        , rpad('x', 4000) as large_vc3
from
          dual
connect by
          level <= 1000
;

exec dbms_stats.gather_table_stats(null, 'a')

and this query and plans with and without the temp table transformation:

with cte
as
(
  select  /* inline */
          id
        , id2
        , large_vc1
        , large_vc2
        , large_vc3
from
          a
where
          1 = 1
)
select
        *
from
        (
          select id, count(*) from cte group by id
        ) a,
        (
          select id2, count(*) from cte group by id2
        ) b
where
        a.id = b.id2
;

-- Plan with TEMP TABLE transformation
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |  1000 | 52000 |  1341   (1)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6609_26FA32 |       |       |            |          |
|   3 |    TABLE ACCESS FULL       | A                         |  1000 |    11M|   452   (0)| 00:00:01 |
|*  4 |   HASH JOIN                |                           |  1000 | 52000 |   889   (1)| 00:00:01 |
|   5 |    VIEW                    |                           |  1000 | 26000 |   444   (1)| 00:00:01 |
|   6 |     HASH GROUP BY          |                           |  1000 |  4000 |   444   (1)| 00:00:01 |
|   7 |      VIEW                  |                           |  1000 |  4000 |   443   (0)| 00:00:01 |
|   8 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6609_26FA32 |  1000 |    11M|   443   (0)| 00:00:01 |
|   9 |    VIEW                    |                           |  1000 | 26000 |   444   (1)| 00:00:01 |
|  10 |     HASH GROUP BY          |                           |  1000 |  4000 |   444   (1)| 00:00:01 |
|  11 |      VIEW                  |                           |  1000 |  4000 |   443   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6609_26FA32 |  1000 |    11M|   443   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

-- Plan with CTE inlined (turn INLINE into hint)
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1000 | 52000 |   907   (1)| 00:00:01 |
|*  1 |  HASH JOIN           |      |  1000 | 52000 |   907   (1)| 00:00:01 |
|   2 |   VIEW               |      |  1000 | 26000 |   453   (1)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |  1000 |  4000 |   453   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| A    |  1000 |  4000 |   452   (0)| 00:00:01 |
|   5 |   VIEW               |      |  1000 | 26000 |   453   (1)| 00:00:01 |
|   6 |    HASH GROUP BY     |      |  1000 |  4000 |   453   (1)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| A    |  1000 |  4000 |   452   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Looking at the query and plan output the following becomes obvious:

- The mere existence of a WHERE clause, even if it is just "WHERE 1 = 1" and referencing the CTE more than once triggers the transformation (nothing new, already demonstrated in the mentioned previous note, as well as the fact that the inlined CTE variant is cheaper in cost)

- There is a huge difference between the estimated size of the TEMP TABLE and the size of the row sources when using the CTE inline

The latter is particular noteworthy: Usually Oracle is pretty clever in optimizing the projection and uses only those columns required (doesn't apply to the target expression of MERGE statements, by the way), which is reflected in the plan output for the inline CTEs - the wide columns don't matter here because they aren't referenced, although being mentioned in the CTE. But in case of the temp table transformation obviously all columns / expressions mentioned in the CTE become materialized, although not necessarily being referenced when the CTE gets used.

So it would be nice if Oracle only materialized those columns / expressions actually used.

Now you might raise the question why mention columns and expressions in the CTE that don't get used afterwards: Well, generic approaches sometimes lead to such constructs - imagine the CTE part was static, including all possible attributes, but the actual usage of the CTE can be customized by a client. In such cases where only a small part of the available attributes get actually used a temp table transformation can lead to a huge overhead in size of the generated temp table. Preventing the transformation addresses this issue, but then the inlined CTE will have to be evaluated as many times as referenced - which might not be desirable either.

Heuristic TEMP Table Transformation

There are at least three different ways how the Oracle optimizer can come up with a so called TEMP table transformation, that is materializing an intermediate result set:

- As part of a star transformation the repeated access to dimensions can be materialized

- As part of evaluating GROUPING SETs intermediate result sets can be materialized

- Common Subquery/Table Expressions (CTE, WITH clause)

Probably the most common usage of the materialization is in conjunction with the WITH clause.

This is nothing new but since I came across this issue several times recently, here's a short demonstration and a reminder that this so called "TEMP Table Transformation" - at least in the context of the WITH clause - isn't really cost-based, in contrast to most other optimizer transformations nowadays - although the unnest transformation of subqueries also has a "no-brainer" variant where costing isn't considered.

The logic simply seems to be: If the CTE expression is referenced more than once AND the CTE expression contains at least some (filter or join) predicate then it will be materialized.

While in most cases this makes sense to avoid the otherwise repeated evaluation of the CTE expression, there are cases where additional predicates that could be pushed inside the CTE would lead to different, significantly more efficient access paths than materializing the full CTE expression without applying the filters and filtering on the TEMP table afterwards.

Here are just two very simple examples that demonstrate the point, both based on this sample table setup:

create table t1
as
select 
        rownum as id
      , rpad('x', 100) as filler
from 
        dual
connect by 
        level <=1e5;

exec dbms_stats.gather_table_stats(null, 't1')

create index t1_idx on t1 (id);

The index on T1.ID opens up potentially a very precise access to rows.

Here is example number one:

with 
a as 
(
  select  /* inline */ 
          id
        , filler 
  from    
          t1 
  where 
          filler != 'x'
)
select
        t1.*
      , a1.filler
      , a2.filler
from
        t1
      , a a1
      , a a2
where
       a1.id = t1.id
and    a2.id = t1.id
and    t1.id = 1
and    a1.id = 1
and    a2.id = 1
;

-- 11.2.0.3 Plan without INLINE hint
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |     1 |   236 |  1207   (1)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION     |                           |       |       |            |          |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D6619_229329 |       |       |            |          |
|*  3 |    TABLE ACCESS FULL           | T1                        | 99999 |    10M|   420   (1)| 00:00:01 |
|*  4 |   HASH JOIN                    |                           |     1 |   236 |   787   (1)| 00:00:01 |
|*  5 |    HASH JOIN                   |                           |     1 |   171 |   394   (1)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T1                        |     1 |   106 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | T1_IDX                    |     1 |       |     1   (0)| 00:00:01 |
|*  8 |     VIEW                       |                           | 99999 |  6347K|   392   (1)| 00:00:01 |
|   9 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9D6619_229329 | 99999 |    10M|   392   (1)| 00:00:01 |
|* 10 |    VIEW                        |                           | 99999 |  6347K|   392   (1)| 00:00:01 |
|  11 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6619_229329 | 99999 |    10M|   392   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

-- 11.2.0.4 Plan without INLINE hint
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |  9999M|  2197G|       | 28468  (92)| 00:00:02 |
|   1 |  TEMP TABLE TRANSFORMATION     |                           |       |       |       |            |          |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D661A_229329 |       |       |       |            |          |
|*  3 |    TABLE ACCESS FULL           | T1                        | 99999 |    10M|       |   420   (1)| 00:00:01 |
|*  4 |   HASH JOIN                    |                           |  9999M|  2197G|  7520K| 28048  (93)| 00:00:02 |
|*  5 |    VIEW                        |                           | 99999 |  6347K|       |   392   (1)| 00:00:01 |
|   6 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D661A_229329 | 99999 |    10M|       |   392   (1)| 00:00:01 |
|*  7 |    HASH JOIN                   |                           | 99999 |    16M|       |   394   (1)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1                        |     1 |   106 |       |     2   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IDX                    |     1 |       |       |     1   (0)| 00:00:01 |
|* 10 |     VIEW                       |                           | 99999 |  6347K|       |   392   (1)| 00:00:01 |
|  11 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9D661A_229329 | 99999 |    10M|       |   392   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

-- 11.2.0.3/11.2.0.4 Plan with INLINE hint
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   318 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |        |       |       |            |          |
|   2 |   NESTED LOOPS                 |        |     1 |   318 |     6   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   212 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |   106 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |   106 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID  | T1     |     1 |   106 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

The filter in the CTE expression is just there to fulfill the rules I've stated above, without it the TEMP table transformation wouldn't be considered at all. It could also be a (non-filtering) join condition, for example.

Notice the big difference in cost estimates between the plans with and without materialization. Clearly a cost-based evaluation should have rejected the TEMP table transformation, simply because it is a bad idea to materialize 100K rows and afterwards access this TEMP table twice to filter out exactly a single row, instead of accessing the original, untransformed row source twice via precise index access.

This is by the way an example of another anomaly that was only recently introduced (apparently in the 11.2.0.4 patch set / 12.1 release): Notice the bad cardinality estimate in the 11.2.0.4 plan with the TEMP table transformation - the filter on the TEMP table isn't evaluated properly (was already there in previous releases) and in addition the join cardinality is way off - 10G rows instead of a single row is not really a good estimate - and as a side effect the HASH JOIN uses a bad choice for the build row sources.

Another possible, perhaps less common variant is this example:

with 
a as 
(
  select  /* inline */ 
          id
        , filler 
  from 
          t1 
  where 
          filler != 'x'
)
select
        id
      , (select filler from a where id = x.id) as scal_val1
      , (select filler from a where id = x.id) as scal_val2
from
        t1 x
;

-- 12.1.0.2 Plan without INLINE hint
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |   100K|   488K|    77M  (1)| 00:50:26 |
|*  1 |  VIEW                      |                           | 99999 |  6347K|   392   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D660F_229329 | 99999 |    10M|   392   (1)| 00:00:01 |
|*  3 |  VIEW                      |                           | 99999 |  6347K|   392   (1)| 00:00:01 |
|   4 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D660F_229329 | 99999 |    10M|   392   (1)| 00:00:01 |
|   5 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   6 |   LOAD AS SELECT           | SYS_TEMP_0FD9D660F_229329 |       |       |            |          |
|*  7 |    TABLE ACCESS FULL       | T1                        | 99999 |    10M|   420   (1)| 00:00:01 |
|   8 |   TABLE ACCESS FULL        | T1                        |   100K|   488K|   420   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

-- 12.1.0.2 Plan with INLINE hint
----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100K|   488K|   398K  (1)| 00:00:16 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |   106 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |   106 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN                  | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL                  | T1     |   100K|   488K|   420   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

This time I've shown plans from 12.1.0.2 - the latest available release as I write this - to demonstrate that this hasn't changed yet. What has changed in 12c is that the scalar subqueries are now actually represented in the final cost - in pre-12c these costs wouldn't be part of the total cost. So although due to that the cost difference between the two plans in 12c is much more significant than in pre-12c the optimizer still opts for materializing the CTE expression and running full table scans in the scalar subqueries on that temp table instead of taking advantage of the precise access path available - again very likely a pretty bad idea at runtime.

So whenever you make use of the WITH clause make sure you've considered the access paths that might be available when not materializing the result set.

Footnote

As of Oracle 12.1 the MATERIALIZE and INLINE hints are still not officially documented.