Oracle SQL

    Tag Archives: materialization

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

    Posted on February 9, 2013 by Sayan Malakshinov Posted in CBO, oracle, query optimizing, undocumented 6 Comments

    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]

    Update: I did some additional tests and found:

    1. with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
    2. with “xmltable” behavior is very strange – decision about materialization depends on ‘columns …’ clause: when it exists – materialization occurs, if not – doesn’t.
    3. 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]

    materialization materialize

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL