12c - Multiple Indexes on the same set of Columns

Antes do Oracle Database 12c, ao tentar criar um índice para uma coluna ou lista de colunas já indexada, ocorria o erro ORA-01408: such column list already indexed error.

Uma das novas features do 12c, é a capacidade de ter vários índices em uma mesma coluna ou lista de colunas.

A criação de um índice sobre uma coluna ou lista de colunas que já foram indexadas é simples e você tem que garantir que apenas um índice será visível.

Se o índice já criado é visible, então você terá que usar a palavra-chave invisible para a criação do novo índice.

Algumas razões para a utilização desse novo recurso:

SQL> create table tabela_teste
(
coluna1 number,
coluna2 number,
coluna3 number,
coluna4 number
); 

Table created.

- Unique versus nonunique

SQL> create unique index tabela_teste_idx1 on tabela_teste(coluna1);

Index created.

SQL> create unique index tabela_teste_idx1 on tabela_teste(coluna1);

Index created.

 - B-tree versus bitmap

SQL> create index tabela_teste_idx3 on tabela_teste(coluna2);
Index created.

SQL> create bitmap index tabela_teste_idx4 on tabela_teste(coluna2) invisible;
Index created.

  - Different partitioning strategies:

      - Indexes that are not partitioned and indexes that are partitioned

      - Indexes that are locally partitioned and indexes that are globally partitioned

      - Indexes that differ in partitioning type (range or hash)

SQL> create index tabela_teste_idx5 on tabela_teste(coluna3, coluna4)
global partition by range(coluna3, coluna4)
(partition part1 values less than(100, 200),
partition part2 values less than(maxvalue, maxvalue)); 2 3 4

Index created.

SQL> create index tabela_teste_idx6 on tabela_teste(coluna3, coluna4)
global partition by hash(coluna3, coluna4)
partitions 10 invisible; 2 3

Index created.

Modificando qual índice está visível:

SQL> ALTER INDEX tabela_teste_idx1 INVISIBLE;

Index altered.

SQL> ALTER INDEX tabela_teste_idx2 VISIBLE;

Index altered.

Fazendo o optimizador considerar os índices invisíveis:

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select count(*) from tabela_teste;

Execution Plan
----------------------------------------------------------
Plan hash value: 3185905804

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT | | 1 | |
| 3 | BITMAP INDEX FAST FULL SCAN| TABELA_TESTE_IDX4 | | |
--------------------------------------------------------------------------------



SQL> alter session set optimizer_use_invisible_indexes=false;
Session altered.

SQL> select count(*) from tabela_teste;

Execution Plan
----------------------------------------------------------
Plan hash value: 1737391099

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TABELA_TESTE | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Referências:

http://docs.oracle.com/cd/E16655_01/server.121/e17636/indexes.htm#ADMIN13845

Abraço

Alex Zaballa