First_Rows

Browsing through the archive for the Oracle-L listserver a couple of days ago I came across this item dated Feb 2011 where the author was puzzled by Oracle’s choice of index for a query.

He was using 10.2.0.3, and running with the optimizer_mode set to first_rows – which you shouldn’t really be doing with that version of Oracle since Oracle Corp. told us about 10 years ago that “first_rows is avaiable only for backwards compatibility”.

I’ve created a model of their problem to demonstrate the effect. As usual, to make it easier to get a reproducible result, I’ve used locally managed tablespaces with 1MB uniform extents, freelist management, and CPU costing disabled:

rem
rem     Script:         first_rows_bug_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2011
rem     Purpose:        
rem
rem     Last tested 
rem             10.2.0.3
rem

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                rownum <= 10000
)
select
        to_char(
                to_date('01-Jan-2011','dd-mon-yyyy') +
                        trunc((rownum-1)/317),
                'yyyymmdd'
        )                       a,
        mod(rownum,317) + 1     b,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        generator       v1,
        generator       v2
where
        rownum <= 100000
;

alter table t1 add constraint t1_pk primary key(a,b);
create index t1_ba on t1(b,a);

The SQL creates 317 rows for a list of dates which have been stored as eight character strings in the form YYYYMMDD. The 317 rows are numbered from 1 to 317, and the data is stored in order of date and number. I’ve created a primary key on (date, number), and I’ve also created an index on (number, date) – the PK has a very good clustering_factor and the other index has a very bad one because of the way I generated the data.

With this data in hand, and after collecing statistics (compute, no histograms), I run the following SQL (and like the OP I am using 10.2.0.3):

alter session set optimizer_mode = first_rows;

set autotrace traceonly explain

select
        small_vc
from
        t1
where
        a = '20110401'
and     b > 10
order by
        a, b
;

select
        /*+ index(t1(a,b)) */
        small_vc
from
        t1
where
        a = '20110401'
and     b > 10
order by
        a, b
;

I’m after 307 consecutive rows of one date – and I want the data sorted by the date and number. With first_rows optimization the default plan is a little surprising. Here are two execution plans for the query – first the plan that the optimizer chose by default, the second when I hinted the SQL to use the primary key – note that neither plan shows a sort operation:

Default execution plan
---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   307 |  7368 |   617 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   307 |  7368 |   617 |
|*  2 |   INDEX SKIP SCAN           | T1_BA |   307 |       |   309 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B">10 AND "A"='20110401')
       filter("A"='20110401' AND "B">10)

Hinted execution plan
---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   307 |  7368 |    10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   307 |  7368 |    10 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |   307 |       |     2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"='20110401' AND "B">10)

How strange – it is clearly better to use the primary key index for this query, yet the optimizer doesn’t do it under first_rows optimisation. (It does if you use the slightly more appropriate first_rows(1) – the “new” improved option from 9i).

The first thought you might have when looking at this example is the first_rows has a heuristic (i.e. rule) that says “use an index to avoid sorting at all costs if possible (unless the hidden parametere _sort_elimination_cost_ratio is non-zero)”. But that shouldn’t apply here because both indexes will allow Orace to avoid sorting.

And here’s an even stranger detail: notice that the “order by” clause includes column “a”, which is obviously constant because of the “where” clause. Since it’s constant removing it won’t make any difference to the final output – but look what happens:

select
        small_vc
from
        t1
where
        a = '20110401'
and     b > 10
order by
        b
;


---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   307 |  7368 |    10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   307 |  7368 |    10 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |   307 |       |     2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"='20110401' AND "B">10)
       filter("A"='20110401' AND "B">10)

For no obvious reason the optimizer now picks the right index. What’s going on ? Unfortunately I have to say that I don’t know.

When I checked the 10053 trace file there were a few differences for the two “order by” clauses but I couldn’t see anything that gave me any reasonable ideas. The most significant difference was the choice of indexes examined when the optimizer was doing the “recost for order by” bit. When we ordered by a,b the optimizer considered only the t1_ba index (note – the final costs are slightly higher here because in this run I enabled CPU costing to see if that was having an effect, so there’s a little extra for the CPU):

  Access Path: index (skip-scan)
    SS sel: 0.0030744  ANDV (#skips): 308
    SS io: 308.00 vs. index scan io: 321.00
    Skip Scan chosen
  Access Path: index (SkipScan)
    Index: T1_BA
    resc_io: 617.00  resc_cpu: 23882848
    ix_sel: 0.0030744  ix_sel_with_filters: 0.0030744
    Cost: 618.60  Resp: 618.60  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_BA
         Cost: 618.60  Degree: 1  Resp: 618.60  Card: 307.44  Bytes: 24

when we ordered by b alone the optimizer considered only the t1_pk index:

  Access Path: index (RangeScan)
    Index: T1_PK
    resc_io: 10.00  resc_cpu: 191334
    ix_sel: 0.0030744  ix_sel_with_filters: 0.0030744
    Cost: 10.01  Resp: 10.01  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_PK
         Cost: 10.01  Degree: 1  Resp: 10.01  Card: 307.44  Bytes: 24

There really seems to be a flaw in the logic behind the choice of index – and there’s an important point to think about here: if it’s a bug it’s probably not going to be fixed. The first_rows option only exists for “backwards compatibility” and things stop being compatible if you change them.

Footnote: Because the cost of the skip scan path in the original run was 617 and the cost of the primary key range scan path was 10 I could make Oracle choose the primary key by setting the parameter _sort_elimination_cost_ratio to a value just less than 617/10 (say 60); but I mention that only as an idle curiosity. You shouldn’t be using first_rows , and if you do use it you shouldn’t be hacking with undocumented parameters to work around the problems it produces.