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.
