Oracle SQL

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:: Hidden text	Select Show
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 \| ---------------------------------------------------------------------------------------------------------

Spoiler:: Hidden text

Select Show


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 |
---------------------------------------------------------------------------------------------------------

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 from ‘columns …’ clause: when it exists – materialization occurs, if not – not occurs.
with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence

Spoiler:: Tests with table() were like this:	Select Show
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;

Spoiler:: Tests with table() were like this:

Select Show

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;

Spoiler:: Tests with xmltable() were like this:	Select Show
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;

Spoiler:: Tests with xmltable() were like this:

Select Show

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;

Spoiler:: Test with 2 from 1000 columns	Select Show
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;

Spoiler:: Test with 2 from 1000 columns

Select Show

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;

Oracle SQL

Tag Archives: materialization

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