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 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 | --------------------------------------------------------------------------------------------------------- [collapse]

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: 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; [collapse]

Tests with xmltable() were like this: 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; [collapse]

Test with 2 from 1000 columns 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; [collapse]

I did some additional tests and found: