Weloverwogen : Een duurzamere wereld begint bij jezelf
email : Password :
Oracle
Java
IT Misc
Natuur
Privacy
INDEX FULL SCAN (MIN/MAX)

Today I came across somewhat strange behavior with the CBO. See the following:

drop table cbo_tab_1;

prompt create table
create table cbo_tab_1 (id number,
                        sca number(10,0),
                        clu number(10,0),
                        null_col number(10,0),
                        filler char(500)
                       );

prompt insert into table
insert into cbo_tab_1 select level,
                             mod(level,10),
                             round(level/100),
                             decode(mod(level, 100), 0, null, level),
                             'filler'
                      from   dual
                      connect by level<=100000;


commit;

prompt create indexes
create unique index CBO_TAB_1_ID on CBO_TAB_1(id);

prompt gather stats
exec dbms_stats.gather_table_stats('MARTIJN','CBO_TAB_1', block_sample=>false, cascade=>true, method_opt=>'for all columns size 1');
(I left out some stuff of the script which I do not need here)

So far, so good. Now I'm interested in the min(id) and max(id).
First go for the most basic approach: I query the table separately for min() and max(). (And of course look what the plan looks like)

SQL> explain plan for select min(id) from cbo_tab_1;
SQL> @xpln

ID         OPERATION                                OBJECT                                   ALIAS                CARDINALITY       COST OPTIMIZER            BYTES
---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ---------- --------------- ----------
--- 0       SELECT STATEMENT()                                                                                              1          2 ALL_ROWS                 5
--P 1        SORT(AGGREGATE)                                                                                                1                                     5
--P 2         INDEX(FULL SCAN (MIN/MAX))            (INDEX (UNIQUE)) MARTIJN.CBO_TAB_1_ID    CBO_TAB_1@SEL$1                1          2 ANALYZED                 5

    ID PROJECTION
------ ---------------------------------------------------------------------------
     1 (#keys=0) MIN("ID")[22]
     2 "ID"[NUMBER,22]
SQL> 
SQL> 
SQL> explain plan for select max(id) from cbo_tab_1;
SQL> @xpln

ID         OPERATION                                OBJECT                                   ALIAS                CARDINALITY       COST OPTIMIZER            BYTES
---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ---------- --------------- ----------
--- 0       SELECT STATEMENT()                                                                                              1          2 ALL_ROWS                 5
--P 1        SORT(AGGREGATE)                                                                                                1                                     5
--P 2         INDEX(FULL SCAN (MIN/MAX))            (INDEX (UNIQUE)) MARTIJN.CBO_TAB_1_ID    CBO_TAB_1@SEL$1                1          2 ANALYZED                 5

    ID PROJECTION
------ ---------------------------------------------------------------------------
     1 (#keys=0) MAX("ID")[22]
     2 "ID"[NUMBER,22]
SQL> 


This to me looks sane. The optimizer chooses to use the index with a "full scan (min/max)". The table is not used at all, so the costs are low.

Now I would like to get the min() and max() values in one query:

SQL> explain plan for select min(id), max(id) from cbo_tab_1;
SQL> @xpln

ID         OPERATION                                OBJECT                                   ALIAS                CARDINALITY       COST OPTIMIZER            BYTES
---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ---------- --------------- ----------
--- 0       SELECT STATEMENT()                                                                                              1        325 ALL_ROWS                 5
--P 1        SORT(AGGREGATE)                                                                                                1                                     5
--P 2         TABLE ACCESS(FULL)                    (TABLE) MARTIJN.CBO_TAB_1                CBO_TAB_1@SEL$1           100000        325 ANALYZED            500000

    ID PROJECTION
------ ---------------------------------------------------------------------------
     1 (#keys=0) MAX("ID")[22], MIN("ID")[22]
     2 "ID"[NUMBER,22]
SQL>


Now the CBO decides to use an FTS, which is way more expensive then the first two querys added together.
I found following blog from Richard Foote, who describes the same problem. Also Martin Widlake writes about this issue.

From that blog I learned that making the column "not null" is a workaround to the problem:

SQL> alter table cbo_tab_1 modify id not null;
SQL> 
SQL> explain plan for select min(id), max(id) from cbo_tab_1;
SQL> @xpln

ID         OPERATION                                OBJECT                                   ALIAS                CARDINALITY       COST OPTIMIZER            BYTES
---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ---------- --------------- ----------
--- 0       SELECT STATEMENT()                                                                                              1         10 ALL_ROWS                 5
--P 1        SORT(AGGREGATE)                                                                                                1                                     5
--P 2         INDEX(FAST FULL SCAN)                 (INDEX (UNIQUE)) MARTIJN.CBO_TAB_1_ID    CBO_TAB_1@SEL$1           100000         10 ANALYZED            500000

    ID PROJECTION
------ ---------------------------------------------------------------------------
     1 (#keys=0) MAX("ID")[22], MIN("ID")[22]
     2 "ID"[NUMBER,22]
SQL>


A totally different access method is used, the "index fast full scan". This is ofcourse a very fast way to retrieve the informationwe need. However, it's not allways feasible to make a column a "not null" column. So this work around is somewhat limited.

Looking into support.oracle.com I found not to much information. Still, note 316467.1 was in the search results. This note doesn't supply any solutions or an explanation of the problem, it just states that the problem exists. (According to the note versions 8.1.7.4 to 10.1.0.4). Well...my environment is 11.2.0.4, and the problem also persists to this version.

Ofcourse there are some other workarounds, but I would like to know why the index is not used when more then one aggregate functions are used in a select. For example:

SQL> alter table cbo_tab_1 modify id null;
SQL> select mi.c, 
            ma.c 
     from   (
              select min(id) c 
              from   cbo_tab_1
            ) mi,
            (
              select max(id) c 
              from   cbo_tab_1) ma;

         C          C
---------- ----------
         1     100000
SQL> explain plan for select mi.c, ma.c from (select min(id) c from cbo_tab_1) mi, (select max(id) c from cbo_tab_1) ma;
SQL> @xpln

ID         OPERATION                                OBJECT                                   ALIAS                CARDINALITY       COST OPTIMIZER            BYTES
---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ---------- --------------- ----------
--- 0       SELECT STATEMENT()                                                                                              1          4 ALL_ROWS                26
--P 1        NESTED LOOPS()                                                                                                 1          4                         26
--P 2         VIEW()                                                                         MI@SEL$1                       1          2                         13
--P 3          SORT(AGGREGATE)                                                                                              1                                     5
--P 4           INDEX(FULL SCAN (MIN/MAX))          (INDEX (UNIQUE)) MARTIJN.CBO_TAB_1_ID    CBO_TAB_1@SEL$2                1          2 ANALYZED                 5
--P 5         VIEW()                                                                         MA@SEL$1                       1          2                         13
--P 6          SORT(AGGREGATE)                                                                                              1                                     5
--P 7           INDEX(FULL SCAN (MIN/MAX))          (INDEX (UNIQUE)) MARTIJN.CBO_TAB_1_ID    CBO_TAB_1@SEL$3                1          2 ANALYZED                 5

    ID PROJECTION
------ ---------------------------------------------------------------------------
     1 (#keys=0) "MI"."C"[NUMBER,22], "MA"."C"[NUMBER,22]
     2 "MI"."C"[NUMBER,22]
     3 (#keys=0) MIN("ID")[22]
     4 "ID"[NUMBER,22]
     5 "MA"."C"[NUMBER,22]
     6 (#keys=0) MAX("ID")[22]
     7 "ID"[NUMBER,22]
SQL>

Ha...that works like a charm, and it's even cheaper (!!!) then the workaround with the "column not null" solution.

Anyway...to me this seems like a bug. Reading through Martin Widlake's post I find that Oracle thinks not.




INDEX FULL SCAN (MIN/MAX)
Martijn2013-11-27 10:41:24
2013-11-27 12:00:002013-11-27 12:00:00

Naam/Email
Reactie
Please answer the following : 7+4=