1 2 Previous Next 20 Replies Latest reply on Mar 2, 2014 4:51 PM by Jonathan Lewis

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

    Ahmed AANGOUR

      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?

        • 1. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
          Jonathan Lewis

          For a tie in the cost of the index: at one time the choice was alphabetical by name but a fix came in some time in 10g to select the index with the larger number of distinct keys.

           

          At present is seems to be:

           

          • If all indexes are unique and the costs are the same then tie-break on number of distinct keys, if those match then alphabetical.
          • If all indexes are non-unique and the costs are the same then tie-break on number of distinct keys, if those match then alphabetical.
          • If there is a mixture of unique and non-unique then NON-unique are preferred

           

          Regards

          Jonathan Lewis

          1 person found this helpful
          • 2. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
            Martin Preiss

            Jonathan,

            • If there is a mixture of unique and non-unique then NON-unique are preferred

            Is there a rationale for the preference of the non-unique indexes? I would have expected it the other way round.

             

            Regards

             

            Martin

            • 3. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
              Jonathan Lewis

              Martin Preiss wrote:

               

              Jonathan,

                • If there is a mixture of unique and non-unique then NON-unique are preferred

              Is there a rationale for the preference of the non-unique indexes? I would have expected it the other way round.

               

               

               

              So did I.

               

              Maybe it's because a non-unique index could be viewed as "1 row or less" while the unique index is assumed to be a whole 1 row.

               

              Regards

              Jonathan Lewis

              • 4. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                Ahmed AANGOUR

                Hi Jonathan,

                 

                Thanks for your contribution on this topic.

                However I'm sceptical concerning your last sentence.

                I've just finished to read the following article written by Richard Foote on December 2007:

                http://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/

                 

                In this article Richard says that for the CBO an index unique scan can return 0 or 1 row, not more. But with a non-unique index you can have 0 or 1 or more than 1 rows, and, because of that, when Oracle is dealing with an index range scan there is an overhead because it must check the next index entry.

                Richard also demonstrates that index range scans generate much more latches than index unique scan because of the cheaper special type of consistent gets induced by index unique scan (consistent gets - examination)

                So after reading Richard Foote's article I cannot understand the logical to choose a non unique index over a unique index when the cost is the same.

                • 5. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                  Jonathan Lewis

                  Ahmed AANGOUR wrote:

                   

                  Hi Jonathan,

                   

                  Thanks for your contribution on this topic.

                  However I'm sceptical concerning your last sentence.

                   

                  So am I; that's why it starts with the word "MAYBE".

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                    Jonathan Lewis

                    Ahmed AANGOUR wrote:

                     

                     

                    So after reading Richard Foote's article I cannot understand the logical to choose a non unique index over a unique index when the cost is the same.

                     

                    This being the case you have three options -

                    a) Create an example to demonstrate the phenomenon and then raise an SR asking Oracle support why the code prefers the non-unique index

                    b) Examine my test case very closely (which you can't do because I haven't published it) to see if there's a design flaw in it, or if it's a special case

                    c) Come up with a hypothesis that MIGHT make sense and see if you can figure out a way of testing it.

                     

                    In my case (b) I used a primary key constraint to generate the index - so here's a longer way of stating my previous suggestion:

                     

                    1) The arithmetic says that the number of rows returned will be the same (namely 1) and the cost will be the same regardless of the index used.

                    2) People create primary keys to be sure that they can identify a single item exactly.

                    3) People create non-unique indexes to find out if there is any matching data.

                    There is a possibility that a primary key is being used because it WILL find a row - because that's what you expect with primary. (see 2)

                    There is a possibility that the non-unique index is being used but may not find any rows (see 3)

                    Therefore it is smarter to use the index for which we make the assumption that there may not be any data.

                     

                    (And I'm still sceptical of the argument).

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                      Richard Foote-Oracle

                      Hi Ahmed


                      I think the moral of the story here is just to be very very careful with analyzed objects that have 0 for statistics but actually contain data. It's a dangerous scenario as the CBO acts as if it just doesn't really care and takes shortcuts as "the logic" is that there's no data right


                      My answer to the question on why it appears to select the non-unique index when the stats reports 0 rows is that it doesn't really matter why when there is no data and if there is data then the statistics shouldn't be left at 0.


                      Regarding why one index vs. another, here's a little demo that should show a unique index is preferred when there are identical indexes available (11.2, 12.1 databases) but with non-zero statistics in place:

                       

                      SQL> create table radiohead (id number, code number, name varchar2(30));

                       

                      Table created.

                       


                      SQL> insert into radiohead select rownum, rownum, 'RADIOHEAD' from dual connect by level <= 750;

                       

                      750 rows created.

                       


                      SQL> commit;

                       

                      Commit complete.

                       


                      SQL> create index radiohead_code_i on radiohead(code);

                       

                      Index created.

                       

                      SQL> create unique index radiohead_id_i on radiohead(id);

                       

                      Index created.

                       


                      SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'RADIOHEAD');

                       

                      PL/SQL procedure successfully completed.


                      SQL> select index_name, blevel, leaf_blocks from dba_indexes where table_name='RADIOHEAD';


                      INDEX_NAME           BLEVEL LEAF_BLOCKS
                      ---------------- ---------- -----------
                      RADIOHEAD_CODE_I          1           2
                      RADIOHEAD_ID_I            1           2


                      SQL> select * from radiohead where id = 42 and code = 42;


                              ID       CODE NAME
                      ---------- ---------- ------------------------------
                              42         42 RADIOHEAD


                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 128221798

                      ----------------------------------------------------------------------------------------------
                      | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                      ----------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT            |                |     1 |    18 |     2   (0)| 00:00:01 |
                      |*  1 |  TABLE ACCESS BY INDEX ROWID| RADIOHEAD      |     1 |    18 |     2   (0)| 00:00:01 |
                      |*  2 |   INDEX UNIQUE SCAN         | RADIOHEAD_ID_I |     1 |       |     1   (0)| 00:00:01 |
                      ----------------------------------------------------------------------------------------------


                      Predicate Information (identified by operation id):
                      ---------------------------------------------------

                         1 - filter("CODE"=42)
                         2 - access("ID"=42)


                      Statistics
                      ----------------------------------------------------------
                                0  recursive calls
                                0  db block gets
                                3  consistent gets
                                0  physical reads
                                0  redo size
                              681  bytes sent via SQL*Net to client
                              544  bytes received via SQL*Net from client
                                2  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                                1  rows processed

                       

                      Cheers


                      Richard Foote

                      http://richardfoote.wordpress.com/

                      • 8. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                        Mohamed Houri

                        Hi Richard,

                         

                        I have had a discussion with Ahmed before he posted his question in this forum. I have modeled his issue as shown below:

                         

                        As very often I used a Jonathan Lewis table script

                         

                        create table t1

                        (id number,

                          n_1000 number,

                          n_5000 number,

                          n_10000 number,

                          small_vc varchar2(20),

                          padding varchar2(100)

                        );

                         

                        create unique index a_unq_ind on t1(id, n_1000); -- index name starts with a

                         

                        create index b_non_unq_ind on t1(n_1000, n_5000); -- index name starts with b

                         

                        insert into t1

                            with generator as (

                                 select   --+ materialize

                                         rownum id

                                 from dual

                                 connect by

                                         rownum <= 10000

                            )

                            select

                                 rownum                    id,

                                 mod(rownum,1000) n_1000,

                                 mod(rownum,5000) n_5000,

                                 mod(rownum,10000) n_10000,

                                 lpad(rownum,10,'0')       small_vc,

                                 rpad('x',100)             padding

                           from

                                 generator        v1,

                                       generator        v2

                           where

                                 rownum <= 100000

                          ;

                         

                        commit;

                        Up to now there is no statistics collected and I have created the indexes before populating the table intentionally.

                         

                        SQL> select * from t1

                          2   where id = 1

                          3   and n_1000 = 1;

                         

                                ID     N_1000 N_5000    N_10000 SMALL_VC             PADDING

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

                                 1          1          1          1 0000000001           x

                         

                        SQL_ID 29tnq7b69swdr, child number 0

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

                        select * from t1 where id = 1  and   n_1000 = 1

                         

                        Plan hash value: 3790258116

                         

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

                        | Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

                        |   0 | SELECT STATEMENT                    |               |      1 |        |      1 |00:00:00.01 |     103 |

                        |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1            |      1 | 15     |      1 |00:00:00.01 |     103 |

                        |*  2 |   INDEX RANGE SCAN                  | B_NON_UNQ_IND |      1 | 615    |    100 |00:00:00.01 |       3 |

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

                         

                        Predicate Information (identified by operation id):

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

                           1 - filter("ID"=1)

                           2 - access("N_1000"=1)

                         

                        And when I force the use of the unique index the CBO seems doing good estimations and generation less buffer gets

                         

                        SQL> select /*+ index( t1 A_UNQ_IND ) */ * from t1  where id = 1  and n_1000= 1;

                         

                                ID     N_1000 N_5000    N_10000 SMALL_VC             PADDING

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

                                 1          1          1          1 0000000001           x

                         

                         

                        SQL_ID bt11jwur90xg0, child number 0

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

                        select /*+ index( t1 A_UNQ_IND ) */ * from t1  where id = 1 and

                        n_1000= 1

                         

                        Plan hash value: 3585360496

                         

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

                        | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time | Buffers |

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

                        |   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |

                        |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       3 |

                        |*  2 | INDEX UNIQUE SCAN           | A_UNQ_IND |      1 |      1 |      1 |00:00:00.01 |       2 |

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

                         

                        Predicate Information (identified by operation id):

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

                           2 - access("ID"=1 AND "N_1000"=1)

                         

                         

                        But when I collect statistics on the table the situation is back to what seems obvious for us since the beginning

                         

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

                         

                        PL/SQL procedure successfully completed.

                         

                        SQL> select * from t1

                          2   where id = 1

                          3   and n_1000 = 1;

                         

                                ID     N_1000 N_5000    N_10000 SMALL_VC             PADDING

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

                                 1          1          1          1 0000000001           x

                         

                         

                        SQL_ID 29tnq7b69swdr, child number 1

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

                        select * from t1 where id = 1  and   n_1000 = 1

                         

                        Plan hash value: 3585360496

                         

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

                        | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time | Buffers |

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

                        |   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |

                        |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       3 |

                        |*  2 | INDEX UNIQUE SCAN           | A_UNQ_IND |      1 |      1 |      1 |00:00:00.01 |       2 |

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

                         

                        Predicate Information (identified by operation id):

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

                           2 - access("ID"=1 AND "N_1000"=1)

                         

                        Note

                        -----

                           - statistics feedback used for this statement

                         

                         

                        I am doing this test case on a 12c data base and seeing this statistics feedback used note, pushed me to flush the shared pool to be sure that the use of the unique index is not due to this statistics feedback feature

                         

                        SQL> alter system flush shared_pool;

                         

                        System altered.

                         

                        SQL> select * from t1

                          2   where id = 1

                          3   and n_1000 = 1;

                         

                                ID     N_1000 N_5000    N_10000 SMALL_VC             PADDING

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

                                 1          1          1          1 0000000001           x

                         

                         

                        SQL_ID 29tnq7b69swdr, child number 0

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

                        select * from t1 where id = 1  and   n_1000 = 1

                         

                        Plan hash value: 3585360496

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

                        | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time | Buffers |

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

                        |   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |

                        |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       3 |

                        |*  2 | INDEX UNIQUE SCAN           | A_UNQ_IND |      1 |      1 |      1 |00:00:00.01 |       2 |

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

                         

                        Predicate Information (identified by operation id):

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

                           2 - access("ID"=1 AND "N_1000"=1)

                         

                         

                        But nevertheless, I still have one question: neither in my example nor in the whole Ahmed 10053 trace file there is no use of Dynamic Sampling. Why Dynamic sampling didn’t kick in?

                         

                        Best Regards

                         

                        Mohamed Houri

                        • 9. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                          Martin Preiss

                          Mohamed,

                           

                          maybe I am wrong but I think that the presence of the indexes with (plain wrong) index statistics seems to be sufficient to the optimizer to avoid the sampling. Without indexes the sampling takes place in your example. For the execution with the indexes the cbo trace (12.1) shows:

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

                          BASE STATISTICAL INFORMATION

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

                          Table Stats::

                            Table: T1  Alias: T1  (NOT ANALYZED)

                            #Rows: 82  #Blks:  1  AvgRowLen:  100.00  ChainCnt:  0.00

                          Index Stats::

                            Index: A_UNQ_IND  Col#: 1 2

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

                            Index: B_NON_UNQ_IND  Col#: 2 3

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

                          So there are (useless) statistics - and for the given query they even result in a fitting plan...

                           

                          Regards

                           

                          Martin

                          • 10. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                            Ahmed AANGOUR

                            Mohamed,

                             

                            When you created your indexes you triggered an automatic compute statistics on those indexes.

                            So as Martin said those statistics seem sufficient for the CBO to choose a plan without using Dynamic sampling.

                            In my case, it's logical to not have dynamic sampling because I had statistics on both my table and my indexes, even if it was 0 values. Zero is different from NULL.

                            • 11. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                              Mohamed Houri

                              Martin

                               

                              Bizarrely when I deleted the index stats, the unique index has been choosen automatically by the CBO

                               

                              SQL> EXEC DBMS_STATS.delete_index_stats(user, 'A_UNQ_IND');

                               

                              PL/SQL procedure successfully completed.

                               

                              SQL> EXEC DBMS_STATS.delete_index_stats(user, 'B_NON_UNQ_IND');

                               

                              PL/SQL procedure successfully completed.

                               

                              SQL> select * from t1

                                2       where id = 1

                                3       and   n_1000 = 1;

                               

                              SQL_ID  8gzc1ztb4qwc8, child number 0

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

                              select * from t1      where id = 1      and   n_1000 = 1

                               

                              Plan hash value: 3585360496

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

                              | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

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

                              |   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.03 |       3 |      1 |

                              |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.03 |       3 |      1 |

                              |*  2 |   INDEX UNIQUE SCAN         | A_UNQ_IND |      1 |      1 |      1 |00:00:00.03 |       2 |      1 |

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

                               

                              Predicate Information (identified by operation id):

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

                                 2 - access("ID"=1 AND "N_1000"=1)

                               

                              And when I dropped the unique index dynamic sampling kiks in

                               

                              SQL> drop index a_unq_ind;

                              Index dropped.

                               

                              SQL> select * from t1

                                2       where id = 1

                                3       and   n_1000 = 1;

                               

                              SQL_ID  8gzc1ztb4qwc8, child number 0

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

                              select * from t1      where id = 1      and   n_1000 = 1

                               

                              Plan hash value: 3790258116

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

                              | Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

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

                              |   0 | SELECT STATEMENT                    |               |      1 |        |      1 |00:00:00.01 |     103 |      1 |

                              |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1            |      1 |      9 |      1 |00:00:00.01 |     103 |      1 |

                              |*  2 |   INDEX RANGE SCAN                  | B_NON_UNQ_IND |      1 |     30 |    100 |00:00:00.01 |       3 |      1 |

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

                               

                              Predicate Information (identified by operation id):

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

                                 1 - filter("ID"=1)

                                 2 - access("N_1000"=1)

                               

                              Note

                              -----

                                 - dynamic statistics used: dynamic sampling (level=2)


                              Best regards

                              Mohamed Houri

                              • 12. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                                Mohamed Houri

                                Ahmed

                                 

                                Yes it is nice to realize that num_rows = 0 is, in the eye of the CBO, a statistic representation as it would be the case for a other not null numbers. Even though that 0 should be considered in my opinion as at least a not accurate statistics so that dynamic sampling should occur

                                 

                                Best regards

                                Mohamed Houri

                                • 13. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                                  Ahmed AANGOUR

                                  I was taliking yesterday with a friend about this issue.

                                  For him the only reason for the CBO to use the non-unique scan is that it looks first at the number of indexed columns before taking into account the access type or the alphabetical order.

                                  It is possible the CBO estimates that the index with a larger number of columns will increase the CPU COST.

                                  That's what we see in the 10053 trace file:

                                  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


                                  The resc_cpu value for the unique index is larger than the non-unique index 's value.


                                  I generated the plan after having restored the  statistics and set the _optimizer_cost_model to IO:

                                   

                                  exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT',tabname=>'TRANSRPDATES',as_of_timestamp=>TO_DATE('21/02/2014 09:19:30', 'DD/MM/YYYY HH24:MI:SS'));

                                  alter session set "_optimizer_cost_model"=io;

                                  @10053

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

                                  @dis_10053

                                  @plan

                                  Plan hash value: 2250495236

                                   

                                   

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

                                  | Id  | Operation                    | Name           | Rows  | Bytes | Cost  |

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

                                  |   0 | DELETE STATEMENT             |                |     1 |    92 |     2 |

                                  |   1 |  DELETE                      | TRANSRPDATES   |       |       |       |

                                  |   2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES   |     1 |    92 |     2 |

                                  |*  3 |    INDEX UNIQUE SCAN         | P_TRANSRPDATES |     1 |       |     2 |

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

                                   

                                   

                                  Predicate Information (identified by operation id):

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

                                   

                                   

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

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

                                   

                                   

                                   

                                   

                                  alter session set "_optimizer_cost_model"=cpu;

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

                                  @plan

                                  Plan hash value: 2252980867

                                   

                                   

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

                                  | 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))

                                   


                                  We can notice that when the "_optimizer_cost_model"=io, the CBO chooses the unique index but when the "_optimizer_cost_model"=cpu then the CBO chooses the non-unique index.

                                  • 14. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
                                    Jonathan Lewis

                                    Ahmed,

                                     

                                    One of the factors in the choice of index under rule-based optimisation was, indeed, about the number of leading columns of the index (though I can't remember if it was based on the absolute number or the fraction of the total), so it's worth considering whether that's a possible tie-break for CBO; however your example is not conclusive.

                                     

                                    a) with CPU costing disabled the two paths have the same cost so it may simply be that Oracle has chosen alphabetically.

                                    b) with CPU costing enabled the cheaper path has been selected (even though the costs appear to be identical to 2 d.p.)

                                     

                                    This means I also have to do some more experiments because my observation above (non-unique chosen) doesn't agree with yours. However I probably didn't look at the 10053 traces at the time and failed to allow for the fact that the CPU costs might have been different in the test I ran.  Other than disabling CPU costing, I'm now wondering how hard it might be to get two "sufficiently different" indexes to have the same costs for their paths.

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next