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 位用户发现它有用
        • 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 上一个 下一个