Oracle SQL | Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate

I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Simple example:

Spoiler

[sourcecode language=”sql”]

SQL> create table tt1 as select 1 id from dual;

Table created.

SQL> exec dbms_stats.gather_table_stats(”,’TT1′);

PL/SQL procedure successfully completed.

SQL> explain plan for
2 with gen as (select * from tt1)
3 select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————-
Plan hash value: 486748850

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
—————————————————————————–

SQL> explain plan for
2 with gen as (select * from tt1 where 1=1)
3 select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————-
Plan hash value: 2673059801

———————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 26 | 7 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6610_6641830 | | | | |
| 3 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
———————————————————————————————————
[/sourcecode]

[collapse]

Update: I did some additional tests and found:

with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
with “xmltable” behavior is very strange – decision about materialization depends on ‘columns …’ clause: when it exists – materialization occurs, if not – doesn’t.
with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence

Tests with table() were like this:

[sourcecode language=”sql”]
with t as (select * from table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
with t as (select * from t10,table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
[/sourcecode]

[collapse]

Tests with xmltable() were like this:

[sourcecode language=”sql”]
with t as (select * from t10,xmltable(:a ) ttt where 1=1)
select count(*)
from t, t t1;
with t as (select * from t10,xmltable(:a columns n int) ttt where 1=0)
select count(*)
from t, t t1;
with t as (select/*+ no_merge */ * from table(cast(:a as sys.ku$_vcnt)),xmltable(:a) where 1=0 )
select count(*) from t,t t2;
[/sourcecode]

[collapse]

Test with 2 from 1000 columns

[sourcecode language=”sql”]
declare
c varchar2(32767):=’create table t_1000_cols as select ‘;
c2 varchar2(32767);
begin
for i in 1..1000 loop
c2:=c2||’,lpad(1,4000,1) c’||i;
end loop;
c:=c||ltrim(c2,’,’)||’ from dual connect by level<=100′;
execute immediate c;
end;
/
exec dbms_stats.gather_table_stats(”,’T_1000_COLS’);
alter session set tracefile_identifier = mat1000;
alter session set events=’10053 trace name context forever, level 1′;

with t as (select c1,c2 from t_1000_cols)
select count(*)
from t, t t2;

with t as (select c1,c2 from t_1000_cols where 1=1)
select count(*)
from t, t t2;
[/sourcecode]

[collapse]