Index Conspiracy

A little light entertainment – but with a tiny bit of information that’s worth knowing – for a Friday evening. This is a demo I used at IOUG 2003 to warm the audience up before the main event.

We start with a table and two indexes – and a clunky little query just to show the index names and object ids.

rem
rem     Script:         index_conspiracy_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2003 / Nov 2021
rem     Purpose:        Demonstrate the anti-microsoft conspiracy.
rem

create table t1 
as
select 
        rownum          n1, 
        rownum          n2,
        rpad('x',10)    small_vc,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 3000
;

create index first_col_index on t1(n1);
create index microsoft_index on t1(n2);

select object_id, table_name, index_name
from
        (
        select  object_id, object_name
        from    user_objects
        where   object_type = 'INDEX'
        )       v1,
        (
        select table_name, index_name
        from   user_indexes
        where  table_name = 'T1'
        )       v2
where
        object_name = index_name
order by 
        object_id
;

You’ll notice that the n1 and n2 columns are identical and that means the corresponding indexes will have identical content, statistics and costs. So let’s use autotrace to check the plans for a few queries – we won’t be using bind variables so it’s a good bet that the plans from autotrace would be what we’d get if we actually ran the queries and pulled the plans from memory:

set autotrace traceonly explain

prompt  ==========================================
prompt  Initial Behaviour (uses "first_col_index")
prompt  ==========================================

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

At the time of the IOUG conference there was quite a lot of antipathy between Microsoft and Oracle, so it didn’t surprise anyone that the query ignored the index called “microsoft_index” and used the following plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 4128733246

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 |   120 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |   120 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | FIRST_COL_INDEX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("N2"=44)
   2 - access("N1"=44)

We can demonstrate that the microsoft_index was an appropriate index for this query by renaming it, of course, and I’ll leave it as an exercise for the user to see what happens as we go through several different names:

prompt  ============================================
prompt  Renaming "microsoft_index" to "better_index"
prompt  ============================================

alter index microsoft_index rename to better_index;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

prompt  ============================================
prompt  Renaming "better_index" to "microsoft_index"
prompt  ============================================

alter index better_index rename to microsoft_index ;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

prompt  =============================================
prompt  Renaming "microsoft_index" to "ellison_index"
prompt  =============================================

alter index microsoft_index rename to ellison_index ;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

prompt  =========================================
prompt  Renaming "ellison_index" to "gates_index"
prompt  =========================================

alter index ellison_index rename to gates_index ;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

set autotrace off

I think the version of Oracle that I demonstrated this on was a late version of 9i. The behaviour is the same in 21c.