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.
Hi Jonathan, with the same cost Oracle uses the index in alphabetical order
Comment by Donatello Settembrino — November 12, 2021 @ 4:11 pm GMT Nov 12,2021 |
Hello Jonathan,
As far as I can remember, in some (or all) of the Oracle versions, when “similar” indexes could be used,
in some cases Oracle gave preference to the index that was created earlier.
This was the case for example when Oracle could pick one of several candidate indexes for policing a PK / UK constraint.
Looks like the optimizer has a different heuristic for choosing among “equal” candidates.
Interesting, anyway !
Thanks a lot & Best Regards,
Iudith Mentzel
Haifa, Israel
Comment by iudith — November 12, 2021 @ 4:49 pm GMT Nov 12,2021 |
Jonathan,
if my memory serves me well, Donatello Settembrino is right – and I am rather sure, that you mentioned this behaviour somewhere (but I didn‘t find the link; maybe because „jonathan lewis index name“ is not a very selective search in google…). So I would guess that the microsoft_index would beat an oracle_index…
Regards
Martin
Comment by Martin Preiss — November 13, 2021 @ 8:29 pm GMT Nov 13,2021 |
finally I found the missing link: https://jonathanlewis.wordpress.com/2012/02/06/index-naming/.
Comment by Martin Preiss — November 13, 2021 @ 8:50 pm GMT Nov 13,2021 |
[…] Renaming an index can change an executiojn plan (Nov 2021) […]
Pingback by Indexing Catalogue | Oracle Scratchpad — January 28, 2022 @ 5:04 pm GMT Jan 28,2022 |