Oracle SQL

    Tag Archives: ifs(min/max)

    INDEX FULL SCAN (MIN/MAX) with two identical MIN()

    Posted on February 4, 2015 by Sayan Malakshinov Posted in bug, CBO, oracle 1 Comment

    I’ve just noticed an interesting thing:

    Assume, that we have a simple query with “MIN(ID)” that works through “Index full scan(MIN/MAX)”:

    SQL> explain plan for
      2  select
      3     min(ID)      as x
      4  from tab1
      5  where ID is not null;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------
    Plan hash value: 4170136576
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |     1 |     4 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE             |         |     1 |     4 |            |          |
    |   2 |   FIRST ROW                 |         |     1 |     4 |     3   (0)| 00:00:01 |
    |*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |     1 |     4 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("ID" IS NOT NULL)
    
    
    Test tables
    create table tab1(id, x, padding)
    as 
       with gen as (select level n from dual connect by level<=1000)
       select g1.n, g2.n, rpad(rownum,10,'x')
       from gen g1,gen g2;
    create index ix_tab1 on tab1(id, x);
    exec dbms_stats.gather_table_stats('','TAB1');
    

    [collapse]

    But look what will happen if we add one more “MIN(ID)”:

    SQL> explain plan for
      2  select
      3     min(ID)      as x
      4   , min(ID)+1000 as x1000
      5  from tab1
      6  where ID is not null;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------
    Plan hash value: 3397888171
    
    ---------------------------------------------------------------------------------
    | Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |         |     1 |     4 |  3075  (17)| 00:00:02 |
    |   1 |  SORT AGGREGATE       |         |     1 |     4 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| IX_TAB1 |   999K|  3906K|  3075  (17)| 00:00:02 |
    ---------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("ID" IS NOT NULL)
    
    

    Continue reading→

    cbo ifs(min/max)

    Amazing optimization of getting distinct values from the index, and TopN for each of them

    Posted on September 21, 2012 by Sayan Malakshinov Posted in CBO, oracle, query optimizing 4 Comments

    A couple of days ago someone posted a question on the forum which at the first glance seemed old, boring, beaten up and down. Here it is:

    There is a news feed. All news are divided into 10 categories (Politics, sport, auto, real estate, etc).
    I need to get top 4 news sorted by time descending for each category with 1 query.
    If you sort the results – you get 4 politics news, then 4 sport news etc.

    But the task was to make it optimal, and the standard solution with usual TopN using row_number can not be called optimal in any way, especially in case of big tables, relatively small number of categories and uneven distribution or just overall low selectivity.

    So after getting several more or less acceptable variants, and spotting the solution using PostgreSQL (I didn’t dig deep into it, as I saw recursion, min and predicate, and that was enough), I got a great variant.

    But first things first:

    1. Getting distinct values from the index  Continue reading→

    distinct values ifs(min/max) irs(min/max) oracle query optimizing

    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