The Vicious Index that Sabotages Plans it is not Part of [@DBoriented]

We all know that adding a new index may change the execution plans of existing queries – sometimes improving the performance, other times causing a performance degradation (and sometimes not making any difference).
But is it true even if the new index is not used by the new execution plan? And even if the new index is invisible?
I’ve recently discovered that the answer is yes for a specific scenario that involves a descending and function-based index.

Here is a simple example – tested on 11.2.0.4 and 12.1.0.2.

create table t (
  n1 number not null,
  n2 number not null,
  n3 number not null,
  str varchar2(100)
);

insert /*+ append */ into t 
  select rownum,rownum,rownum,rownum 
  from dual 
  connect by level<=100000;
commit;

exec dbms_stats.gather_table_stats (user,'T')

create index t_i1 on t (n1, round(n3) desc);

explain plan for
  select null
  from   t
  where  n1 = :n1
  order  by n1,
            round(n3) desc;

select * from table(dbms_xplan.display());

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    10 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_I1 |     1 |    10 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

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

   1 - access("N1"=TO_NUMBER(:N1))

The execution plan simply retrieves results from the T_I1 index. The results are already sorted in the desired way, as they are stored like that in the index, so the plan does not include a sorting step. Also, since we don’t need any column from the table, the plan does not access T at all.

Now let’s add a second index:

create index t_i2 on t (n2, round(n3));

Note that this index contains the same expression as in the first index – round(n3) – but in the “regular” ascending order. As the new index starts with n2, it is quite obvious that the previous plan – simply range scanning the T_I1 index – is still the best option.
But the plan has changed now – it still accesses T_I1 – but it does two more steps: accessing the table and sorting:

explain plan for
  select null
  from   t
  where  n1 = :n1
  order  by n1,
            round(n3) desc;

select * from table(dbms_xplan.display());

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |    18 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY                       |      |     1 |    18 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |    18 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T_I1 |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("N1"=TO_NUMBER(:N1))

Even if the second index is invisible, it still confuses the optimizer:

alter index t_i2 invisible;

explain plan for
  select null
  from   t
  where  n1 = :n1
  order  by n1,
            round(n3) desc;

select * from table(dbms_xplan.display());

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |    18 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY                       |      |     1 |    18 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |    18 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T_I1 |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("N1"=TO_NUMBER(:N1))

Only after dropping the second index, we get our original plan back:

drop index t_i2;

explain plan for
  select null
  from   t
  where  n1 = :n1
  order  by n1,
            round(n3) desc;

select * from table(dbms_xplan.display());

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    10 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_I1 |     1 |    10 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

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

   1 - access("N1"=TO_NUMBER(:N1))

Leave a Reply Cancel reply