Oracle SQL

    Tag Archives: scalar subqueries

    Oracle 12c: scalar subqueries

    Posted on February 11, 2014 by Sayan Malakshinov Posted in 12c, CBO, oracle, undocumented Leave a comment

    We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
    So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):

    Before 12.1

    SQL> explain plan for
      2  select
      3  (select count(*) from XT_TEST) cnt
      4  from dual;
    
    Explained.
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
    Plan hash value: 2843533371
    
    ---------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |           |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
    |   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    10 rows selected.
    

    [collapse]

    12.1

    SQL> alter session set "_optimizer_unnest_scalar_sq"=false;
    
    Session altered.
    
    SQL> explain plan for
      2  select
      3  (select count(*) from XT_TEST) cnt
      4  from dual;
    
    Explained.
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
    Plan hash value: 2843533371
    
    ---------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |    52   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |           |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
    |   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    10 rows selected.
    

    [collapse]

    But it’s interesting that correlated subquery can reference now to a column from parent tables more
    than one level above:
    Before 12.1

    SQL> with t1 as (select/*+ materialize */ 1 a from dual)
      2      ,t2 as (select/*+ materialize */ 2 b from dual)
      3      ,t3 as (select/*+ materialize */ 3 c from dual)
      4  select
      5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
      6  from t1;
      (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
                                                           *
    ERROR at line 5:
    ORA-00904: "T1"."A": invalid identifier
    

    [collapse]

    12.1

    SQL> with t1 as (select/*+ materialize */ 1 a from dual)
      2      ,t2 as (select/*+ materialize */ 2 b from dual)
      3      ,t3 as (select/*+ materialize */ 3 c from dual)
      4  select
      5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
      6  from t1;
    
             S
    ----------
             6
    

    [collapse]

    scalar subqueries

    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