Category: Indexing

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

Indexes and Indexing in Oracle 12c

One of my presentations in ilOUG Tech Days 2015 was “Indexes and Indexing in Oracle 12c”. I talked about new features added in release 12c that are related to indexing – not necessarily new keywords in the CREATE INDEX statement, but also changes that may affect the way we index (or not).

Here is a summary of the features I talked about, or at least had planned to talk about and then ran out of time 🙁

Note that some of these features are available only in the Enterprise Edition or require a licensed option.

If you want to read about these features in detail, there are many good resources. The best, in my opinion, is Richard Foote’s blog – the “bible” of Oracle indexing  – http://richardfoote.wordpress.com/category/12c/

Multiple indexes on the same column list

In pre-12c versions it is impossible to define several indexes on the same column list.

In 12c it is possible, as long as:

    • Only one of the indexes is visible (the ability to make an index invisible was added in 11g)
    • And the indexes are different somehow (unique vs. non-unique, reverse vs. regular, partitioned vs. non-partitioned, etc.)

What is it good for?

It allows to change the type of an index with (almost) no downtime. Prior to 12c we have to drop the existing index and then create the new one, and during this period we have no available index. In 12c we can create the new index as invisible, and then, very quickly, make the old one invisible and the new one visible.

This feature also allows to validate (or disproof) a hypothesis that another type of an index is better than the existing one. We create a new invisible index of the “other” type, make the necessary comparisons, and based on the conclusion we either drop the new index and keep the old one, or drop the old index and make the new one visible.

If we need this functionality before 12c we can use an old trick and create the new index on almost the same column list – adding one “artificial” column – for example, if we have an index on T (COL1, COL2) we can create another index on T (COL1, COL2, NULL).

Online operations

In 12c more DDL operations can be done online (which roughly means less blocking issues in a multi-user environment). Focusing on indexes, these are the relevant changes:

  • The following statements are offline before 12c and online in 12c:
    • ALTER INDEX VISIBLE
    • ALTER INDEX INVISIBLE
  • The following statements are offline by default, but in 12c they can be online by adding the ONLINE keyword:
    • DROP INDEX [ONLINE]
    • ALTER INDEX UNUSABLE [ONLINE]
    • ALTER TABLE MOVE SUB/PARTITION [ONLINE]

Asynchronous global index maintenance

Suppose we have a partitioned table with global indexes.

Before 12c, dropping or truncating a partition of this table is either a fast operation (that makes the global indexes unusable) or an operation that keeps the global indexes usable (but takes longer to execute).

In 12c, it can be both. When specifying UPDATE GLOBAL INDEXES, the partition is dropped/truncated and the indexes remain usable although the index entries are not deleted.

However, there is a trade-off. When accessing the index entries, extra work is done to check whether the entries belong to an existing partition or to a deleted partition. Also, index blocks containing such “orphaned” entries are usually not reused.

The orphaned entries are cleaned out asynchronously (by a maintenance job, by ALTER INDEX REBUILD, by ALTER INDEX COALESCE CLEANUP, or by calling explicitly to DBMS_PART.CLEANUP_GIDX).

Partial indexes

In 12c it is possible to create an index (either LOCAL or GLOBAL) on a subset of partitions (as long as it is non-unique and not used for enforcing primary/unique keys).

Each table partition is defined as either INDEXING ON or INDEXING OFF (explicitly at the partition level or implicitly by inheriting the definition from the table level).

Then, if the index is created with the new INDEXING PARTIAL clause, then only records from “INDEXING ON” partitions are indexed.

This can be useful, for example, for indexing only recent partitions, where the older partitions are rarely accessed. Another example: loading data into a new partition that is defined as “INDEXING OFF” while older partitions are defined as “INDEXING ON” (for making the loading faster while allowing efficient access to the older partitions), and once the loading is done changing the new partition to “INDEXING ON”.

Advanced index compression

Prefix Index Compression is available since Oracle8i. It is implemented in the leaf block level, and may reduce the size of the index. However, this compression is not optimal, and may even increase the size of the index. Why? One reason is that we need to define the “prefix length” (i.e., how many of the leading index key columns are used for the compression) and while the prefix length may be optimal when it is defined, it may become sub-optimal over time. The second reason is that we define the prefix length at the index level, so the same value is used for all the index leafs; obviously one size does not necessarily fit all, and while this value may be optimal for some of the leafs it may be sub-optimal for others.

In 12c we can use advanced index compression, which uses the same mechanism as the prefix index compression but with some enhancements that address the aforementioned drawbacks: each block  may be compressed or not, the prefix length is determined for each block individually based on its content, and it’s all done automatically.

 

The presentation is available in the Presentations page.