Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Why full table scan even with lower index scan cost?

with 4 comments

See following test case.

1. Version

UKJA@ukja102> select * from v$version;

BANNER
-------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2. Create objects

create table t1(c1 int, c2 int, c3 char(100));

insert into t1
select level, level, 'x'
from dual
connect by level <= 1000
;

create index t1_n1 on t1(c1, c2);  <-- Note that c1 is leading column

exec dbms_stats.gather_table_stats(user, 't1');

3. Now we execute following query. Because leading column(c1) is inexistent in predicates, FTS is expected.

select *
from t1
where c2 = 1  <-- Note that leading column is not given!
;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

4. But there is a problem. With index hint given, Oracle expects lower cost with index full scan. The cost is 5 compared to 7 of FTS.

select /*+ index(t1) */ *
from t1
where c2 = 1
;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

5. Now we have a very serious problem.

Why doesn’t Oracle choose index full scan which has much lower?

I’m very familiar with this phenomenon where Oracle denies to use index when leading column is inexistent in predicates. Sometimes we hit index skip scan instead of FTS, but why Oracle denies to use index full scan?

This behavior is consistent in all versions except in 9.2.0.1 where index full scan cost is much higher.

-- 9.2.0 (without index hint)
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |   108 |     7  (15)|
|*  1 |  TABLE ACCESS FULL   | T1          |     1 |   108 |     7  (15)|
-------------------------------------------------------------------------

-- 9.2.0 (with index hint)
--------------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   108 |    28   (4)|
|*  1 |  TABLE ACCESS FULL          | T1          |     1 |   108 |     7  (15)|
|*  2 |   INDEX FULL SCAN           | T1_N1       |     1 |       |    27   (4)|
--------------------------------------------------------------------------------

-- 9.2.0 (10053 trace)
SINGLE TABLE ACCESS PATH
Column:         C2  Col#: 2      Table: T1   Alias: T1
    NDV: 1000      NULLS: 0         DENS: 1.0000e-003 LO:  1  HI: 1000
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: T1     ORIG CDN: 1000  ROUNDED CDN: 1  CMPTD CDN: 1
  Access path: tsc  Resc:  6  Resp:  6
  Skip scan: ss-sel 0  andv 1000 
    ss cost 1000
    table io scan cost 6
  Access path: index (no sta/stp keys)
      Index: T1_N1
  TABLE: T1
      RSC_CPU: 193379   RSC_IO: 27
  IX_SEL:  1.0000e+000  TB_SEL:  1.0000e-003
  BEST_CST: 7.00  PATH: 2  Degree:  1

-- 10.1.0.2 (without index hint)
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- 10.1.0.2 (with index hint)
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 10.1.0.2 (10053 trace)
SINGLE TABLE ACCESS PATH
  COLUMN:         C2(NUMBER)  Col#: 2      Table: T1   Alias: T1
    Size: 4  NDV: 1000  Nulls: 0  Density: 1.0000e-003 Min: 1  Max: 1000
    No Histogram: #BKT: 1
        (1 uncompressed buckets and 2 endpoint values)
  TABLE: T1  Alias: T1    
    Original Card: 1000  Rounded Card: 1  Computed Card: 1.00
  Access Path: table-scan  Resc:  6  Resp:  6
  Access Path: index (skip-scan)
    ss sel 1.0000e-003  andv 1000 
    ss cost 1000 vs. table scan io cost 6
    Skip Scan rejected
  Access Path: index (no start/stop keys)
    Index: T1_N1
    rsc_cpu: 35997   rsc_io: 5
    ix_sel:  1.0000e+000    ix_sel_with_filters:  1.0000e-003
  BEST_CST: 6.03  PATH: 2  Degree:  1

-- 10.2.0.1 (without index hint)
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- 10.2.0.1 (with index hint)
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 10.2.0.1 (10053 trace)
SINGLE TABLE ACCESS PATH
  Column (#2): C2(NUMBER)
    AvgLen: 4.00 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
  Table: T1  Alias: T1    
    Card: Original: 1000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  7.04  Resp: 7.04  Degree: 0
      Cost_io: 7.00  Cost_cpu: 362449
      Resp_io: 7.00  Resp_cpu: 362449
kkofmx: index filter:"T1"."C2"=1
  Access Path: index (skip-scan)
    SS sel: 1.0000e-003  ANDV (#skips): 1000
    SS io: 1000.00 vs. table scan io: 7.00
    Skip Scan rejected
  Access Path: index (FullScan)
    Index: T1_N1
    resc_io: 5.00  resc_cpu: 235797
    ix_sel: 1  ix_sel_with_filters: 1.0000e-003
    Cost: 5.03  Resp: 5.03  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 7.04  Degree: 1  Resp: 7.04  Card: 1.00  Bytes: 0

-- 11.1.0.6 (without index hint)
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- 11.1.0.6 (with index hint)
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 11.1.0.6 (10053 trace)
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 1000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  7.04  Resp: 7.04  Degree: 0
      Cost_io: 7.00  Cost_cpu: 362449
      Resp_io: 7.00  Resp_cpu: 362449
kkofmx: index filter:"T1"."C2"=1

  Access Path: index (skip-scan)
    SS sel: 0.001000  ANDV (#skips): 1000.000000
    SS io: 1000.000000 vs. table scan io: 7.000000
    Skip Scan rejected

  Access Path: index (FullScan)
    Index: T1_N1
    resc_io: 5.00  resc_cpu: 235797
    ix_sel: 1.000000  ix_sel_with_filters: 0.001000
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
    Cost: 5.03  Resp: 5.03  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 7.04  Degree: 1  Resp: 7.04  Card: 1.00  Bytes: 0

The funny thing is that Oracle did consider the index ful scan and found it to have lower cost! But it silently denied to use it.

Oracle would have chosen index skip scan with lower cost!

Is this a bug or designed feature?

About these ads

Written by Dion Cho

February 11, 2009 at 8:53 am

Posted in Optimizer

Tagged with ,

4 Responses

Subscribe to comments with RSS.

  1. Hi Dion,
    oracle use IFFS if you extract column indexed:

    1) extract c2,c1 and c3 column => FTS
    ————————————-
    explain plan for
    select c2,c1,c3 from t1 where c2=1;

    select * from table(dbms_xplan.display(null,null,’last’));
    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 108 | 7 (0)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| T1 | 1 | 108 | 7 (0)| 00:00:01 |
    ————————————————————————–
    Predicate Information (identified by operation id):
    —————————————————
    1 – filter(“C2″=1)

    1) extract c2,c1 (all columns of index) column => IFFS
    ——————————————————
    explain plan for
    select c2,c1 from t1 where c2=1;

    select * from table(dbms_xplan.display(null,null,’last’));
    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
    |* 1 | INDEX FULL SCAN | T1_N1 | 1 | 8 | 1 (0)| 00:00:01 |
    ————————————————————————–
    Predicate Information (identified by operation id):
    —————————————————
    1 – access(“C2″=1)
    filter(“C2″=1)

    Sandro

    February 11, 2009 at 11:15 am

  2. Sandro. Thanks for the comment.

    1. IFFS is acronym for Index Fast Full Scan. Acronym for Index Full Scan is IFS.

    2. As you commented, Oracle seems to deny index full scan only when { index full scan + table access } combination is used even with lower cost. My question is “why?”. Why does Oracle deny to use index even with lower cost in this special cases?

    3. Please, kindly use <code> tag when posting sql things, like:

    select * from table(dbms_xplan.display(null,null,’last’));
    -------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
    |* 1 | INDEX FULL SCAN | T1_N1 | 1 | 8 | 1 (0)| 00:00:01 |
    -------------------------------------------------------
    Predicate Information (identified by operation id):
    -------------------------------------------------------
    1 - access(”C2″=1)
    filter(”C2″=1)

    Dion Cho

    February 11, 2009 at 11:36 am

  3. Sorry, you are correct.
    only one consideration:

    Index skip scan works well if and only if the leading edge of the index (c1 in the example above) has very few
    discrete values and the optimizer understands that (but I don’t know how).


    create table t1(c1 number not null, c2 number not null, c3 char(100)) nologging;
    insert /*+ append */ into t1
    select round(level/10000), level, 'x'
    from dual
    connect by level user,tabname=> 't1',estimate_percent => 100, granularity=> 'ALL', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',degree=> dbms_stats.default_degree, cascade=> true, no_invalidate=>false );
    explain plan for
    select * from t1 where c2=1;
    select * from table(dbms_xplan.display(null,null,'last'));
    -------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 108 | 4 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 108 | 4 (0)| 00:00:01 |
    |* 2 | INDEX SKIP SCAN | T1_N1 | 1 | | 4 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    filter("C2"=1)

    Sandro

    February 11, 2009 at 1:16 pm

  4. Sandro.

    How does Oracle calculate the cost of index skip scan? The basic mechanismcan be described as following.

    Number of blocks to read for retrieving estimated rows + Number of blocks to read for skipping

    With many discrete values of leading column, Oracle should read many blocks for skipping. Hence higher cost.

    With few discreate values of leading column(as in your test case), Oracle would just read small number of branch blocks. There would be almost no need for skipping. Hence lower cost.

    Thus, two main factors contributing to the cost of index skip scan is
    1. Distinct count of leading column
    2. Estimtaed row count

    Dion Cho

    February 11, 2009 at 1:54 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: