1 2 Previous Next 20 Replies Latest reply on Mar 2, 2014 4:51 PM by Jonathan Lewis Go to original post
      • 15. Re: How does the CBO choose an index when the COST is the same for 2 indexes?
        Mohamed Houri

        Ahmed AANGOUR a écrit:

         

        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.

        Ahmed

         

        Indeed,it is reproducible using the above model in 12c

         

        SQL> alter session set "_optimizer_cost_model"=io;

         

        SQL_ID  8gzc1ztb4qwc8, child number 0

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

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

         

         

        Plan hash value: 3585360496

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

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

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

        |   0 | SELECT STATEMENT            |           |       |       |     1 |

        |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   116 |     1 |

        |*  2 |   INDEX UNIQUE SCAN         | A_UNQ_IND |     1 |       |       |

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

         

        Predicate Information (identified by operation id):

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

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

        Note

        -----

           - cpu costing is off (consider enabling it)

         

        SQL> alter index A_UNQ_IND rename to Z_UNQ_IND;

         

        Index altered.

         

        SQL> select * from t1

          2       where id = 1

          3       and   n_1000 = 1;

         

        Plan hash value: 2738234218

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

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

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

        |   0 | SELECT STATEMENT            |           |       |       |     1 |

        |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   116 |     1 |

        |*  2 |   INDEX UNIQUE SCAN         | Z_UNQ_IND |     1 |       |       |

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

         

        Predicate Information (identified by operation id):

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

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

         

        Note

        -----

           - cpu costing is off (consider enabling it)

         

         

        SQL> alter session set "_optimizer_cost_model"=cpu;

        Session altered.

         

        -- unique index has been re-given its initial name

        SQL> select * from t1

          2       where id = 1

          3       and   n_1000 = 1;

         

        SQL_ID  8gzc1ztb4qwc8, child number 1

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

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

         

         

        Plan hash value: 3790258116

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

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

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

        |   0 | SELECT STATEMENT                    |               |       |       |     1 (100)|

        |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1            |    15 |  1740 |     0   (0)|

        |*  2 |   INDEX RANGE SCAN                  | B_NON_UNQ_IND |   615 |       |     0   (0)|

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

         

        Predicate Information (identified by operation id):

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

           1 - filter("ID"=1) 


        Best regards

        Mohamed Houri

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

          strange indeed. Not sure if I find a rhyme...

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

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

             

            Jonathan,

             

            I ran my query again after having disabled CPU costing and renamed the non-unique index to be alphabetically before the unique index and the conclusion is that the unique index is always chosen when CPU costing is disabled.

            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;

            alter index SCDAT.R_TRANSRPDATES_ACCIK rename to A_TRANSRPDATES_ACCIK;

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

             

            Note

            -----

               - cpu costing is off (consider enabling it)

             

             

            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: 1992853963

             

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

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

             

             

            alter index SCDAT.A_TRANSRPDATES_ACCIK rename to R_TRANSRPDATES_ACCIK;

            exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT',tabname=>'TRANSRPDATES',

            as_of_timestamp=>systimestamp-1, force=> TRUE);

             

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

            BASE STATISTICAL INFORMATION

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

            Table Stats::

              Table: TRANSRPDATES  Alias: TRANSRPDATES

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

            Index Stats::

              Index: A_TRANSRPDATES_ACCIK  Col#: 2

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

              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_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:  5036.00  Resp: 5036.00  Degree: 0

                  Cost_io: 5036.00  Cost_cpu: 0

                  Resp_io: 5036.00  Resp_cpu: 0

              Access Path: index (UniqueScan)

                Index: P_TRANSRPDATES

                resc_io: 2.00  resc_cpu: 0

                ix_sel: 0.000000  ix_sel_with_filters: 0.000000

                Cost: 2.00  Resp: 2.00  Degree: 1

              Access Path: index (AllEqRange)

                Index: A_TRANSRPDATES_ACCIK

                resc_io: 2.00  resc_cpu: 0

                ix_sel: 0.010000  ix_sel_with_filters: 0.010000

                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: 0

                ix_sel: 1.000000  ix_sel_with_filters: 1.000000

                Cost: 2.00  Resp: 2.00  Degree: 1

            One row Card: 1.000000

              Best:: AccessPath: IndexUnique

              Index: P_TRANSRPDATES

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

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

              Ahmed AANGOUR wrote:

               

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

               

              Jonathan,

               

              I ran my query again after having disabled CPU costing and renamed the non-unique index to be alphabetically before the unique index and the conclusion is that the unique index is always chosen when CPU costing is disabled.

              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;

              alter index SCDAT.R_TRANSRPDATES_ACCIK rename to A_TRANSRPDATES_ACCIK;

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

               

              Note

              -----

                 - cpu costing is off (consider enabling it)

               

               

              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: 1992853963

               

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

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

               

               

              alter index SCDAT.A_TRANSRPDATES_ACCIK rename to R_TRANSRPDATES_ACCIK;

              exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT', tabname=>'TRANSRPDATES',

              as_of_timestamp=>systimestamp-1, force=> TRUE);

               

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

              BASE STATISTICAL INFORMATION

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

              Table Stats::

                Table: TRANSRPDATES  Alias: TRANSRPDATES

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

              Index Stats::

                Index: A_TRANSRPDATES_ACCIK  Col#: 2

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

                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_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:  5036.00  Resp: 5036.00  Degree: 0

                    Cost_io: 5036.00  Cost_cpu: 0

                    Resp_io: 5036.00  Resp_cpu: 0

                Access Path: index (UniqueScan)

                  Index: P_TRANSRPDATES

                  resc_io: 2.00  resc_cpu: 0

                  ix_sel: 0.000000  ix_sel_with_filters: 0.000000

                  Cost: 2.00  Resp: 2.00  Degree: 1

                Access Path: index (AllEqRange)

                  Index: A_TRANSRPDATES_ACCIK

                  resc_io: 2.00  resc_cpu: 0

                  ix_sel: 0.010000  ix_sel_with_filters: 0.010000

                  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: 0

                  ix_sel: 1.000000  ix_sel_with_filters: 1.000000

                  Cost: 2.00  Resp: 2.00  Degree: 1

              One row Card: 1.000000

                Best:: AccessPath: IndexUnique

                Index: P_TRANSRPDATES

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

               

              Ahmed.

               

              Still a premature conclusion. Honestly, it's very hard to think of all the variations on even a very simple Oracle problem and make sure that you've excluded all other possibilities.

              In this case you have one plan that can be satisfied completely within the index, and another which (notionally, at least) requires a table visit - perhaps when two queries have the same cost then another tie-break is that "index-only" wins.  

               

              By the way, I've not really been paying proper attention to the fact that your query is a DELETE.  Two things to consider: (a) Oracle costs a delete as if it were "select rowid from table", (b) it's just possible that the rules for deletes and/or updates are different from the rules for selects.

               

              I like the way you're showing your example rather than just giving us an informal description with conclusion - it makes it much easier to come up with explanations of other possibilities.

               

              Regards

              Jonathan Lewis

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

                When I said

                I ran my query again after having disabled CPU costing and renamed the non-unique index to be alphabetically before the unique index and the conclusion is that the unique index is always chosen when CPU costing is disabled.

                I was of course talking about my case. I'm absolutely aware that under other circumstances the CBO choices could have been totally different.

                Anyway, thank you for giving of your time in this discussion. I appreciate that.

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

                  Ahmed AANGOUR wrote:

                   

                  When I said

                  I ran my query again after having disabled CPU costing and renamed the non-unique index to be alphabetically before the unique index and the conclusion is that the unique index is always chosen when CPU costing is disabled.

                  I was of course talking about my case. I'm absolutely aware that under other circumstances the CBO choices could have been totally different.

                  Anyway, thank you for giving of your time in this discussion. I appreciate that.

                   

                  Dropping in and out of the thread for a few minutes each time, I'd also lost track of the fact that you were particularly talking about the special case of the stats on the index saying "no data" - and that could either introduce a few special cases for the code, or it might mean that the generic code produces an unexpected side effect.

                   

                  Index two columns which are identical to each other (i.e. create an index on each column), but declare one of those indexes to be unique then the cost of accessing the table with equality on the unique index will be one less than the cost of accessing with equality on the non-unique index because the optimizer has a "subtract one for unique" rule. There's no way to know how this would play out if the stats said "no data" other than to do the experiment - which I haven't done.

                   

                  Regards

                  Jonathan Lewis

                  1 2 Previous Next