Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How does the CBO choose an index when the COST is the same for 2 indexes?

Ahmed AANGOURFeb 26 2014 — edited Mar 2 2014

Hello Folks,

I have a table named TRANSRPDATES with 3 indexes:

Index Name                           Pos# Order Column Name

------------------------------ ---------- ----- ------------------------------

p_transrpdates                          1 ASC   transik

                                        2 ASC   accik

r_transrpdates_accik                    1 ASC   accik

r_transrpdates_rpdefik                  1 ASC   rpdefik

I execute the following query:

DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1  AND TRANSRPDATES.ACCIK = :v2;

If statistics are gathered when the table contains rows the CBO chooses the unique index. That's logical for everybody I guess.

-----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT             |                |     1 |    40 |     3   (0)| 00:00:01 |

|   1 |  DELETE                      | TRANSRPDATES   |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES   |     1 |    40 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | P_TRANSRPDATES |     1 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1) AND

              "TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))

What is confusing for me is that if the statistics are gathered when the table is empty (all table and index statistics = 0) the CBO chooses to use an index range scan on the non unique index:

-----------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT             |                      |     1 |    92 |     2   (0)| 00:00:01 |

|   1 |  DELETE                      | TRANSRPDATES         |       |       |            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES         |     1 |    92 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | R_TRANSRPDATES_ACCIK |     1 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1))

   3 - access("TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))

To have a better understanding on this I decided to generate a 10053 trace file when the CBO chooses the non-unique index.

here is an extract:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TRANSRPDATES  Alias: TRANSRPDATES

    #Rows: 0  #Blks:  33172  AvgRowLen:  0.00  ChainCnt:  0.00

Index Stats::

  Index: P_TRANSRPDATES  Col#: 1 2

    LVLS: 2  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00

  Index: R_TRANSRPDATES_ACCIK  Col#: 2

    LVLS: 2  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00

  Index: R_TRANSRPDATES_RPDEFIK  Col#: 6

    LVLS: 2  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00

***************************************

1-ROW TABLES:  TRANSRPDATES[TRANSRPDATES]#0

Access path analysis for TRANSRPDATES

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for TRANSRPDATES[TRANSRPDATES]

  Column (#1): TRANSIK(

    AvgLen: 22 NDV: 0 Nulls: 0 Density: 0.000000 Min: 0 Max: 0

  Column (#2): ACCIK(

    AvgLen: 22 NDV: 0 Nulls: 0 Density: 0.000000 Min: 0 Max: 0

  ColGroup (#1, Index) P_TRANSRPDATES

    Col#: 1 2    CorStregth: 0.00

  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 1.0000

  Table: TRANSRPDATES  Alias: TRANSRPDATES

    Card: Original: 0.000000  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00

  Access Path: TableScan

    Cost:  9540.36  Resp: 9540.36  Degree: 0

      Cost_io: 9479.00  Cost_cpu: 236232408

      Resp_io: 9479.00  Resp_cpu: 236232408

  Access Path: index (UniqueScan)

    Index: P_TRANSRPDATES

    resc_io: 2.00  resc_cpu: 15583

    ix_sel: 0.000000  ix_sel_with_filters: 0.000000

    Cost: 2.00  Resp: 2.00  Degree: 1

  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 1.0000

  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 1.0000

  Access Path: index (AllEqUnique)

    Index: P_TRANSRPDATES

    resc_io: 2.00  resc_cpu: 15583

    ix_sel: 1.000000  ix_sel_with_filters: 1.000000

    Cost: 2.00  Resp: 2.00  Degree: 1

  Access Path: index (AllEqRange)

    Index: R_TRANSRPDATES_ACCIK

    resc_io: 2.00  resc_cpu: 14443

    ix_sel: 0.010000  ix_sel_with_filters: 0.010000

    Cost: 2.00  Resp: 2.00  Degree: 1

  Best:: AccessPath: IndexRange

  Index: R_TRANSRPDATES_ACCIK

         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0

We can notice that the cost for the 2 indexes is 2.

My guess is that the CBO chooses to use the index which is potentially the smallest, that is to say the one with few index keys.

Index R_TRANSRPDATES_ACCIK has only one key whereas index P_TRANSRPDATES has 2 keys.

The alphabetic order of the index name does not matter here since the R_TRANSRPDATES_ACCIK  is alphabeticaly after the unique index P_TRANSRPDATES.

Does someone have an idea on this?

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 30 2014
Added on Feb 26 2014
20 comments
8,504 views