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

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

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

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

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

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

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/

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

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

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

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

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