Múltiplos índices em uma mesma coluna ou lista de colunas – Recurso avançado de indexação

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