12c - Partial Indexes for Partitioned Tables

Um novo recurso chamado Partial Indexes for Partitioned Tables foi introduzido no Oracle 12c.

Agora, você pode criar os índices (globais ou locais) para partições ou sub-partições específicas, isto é,  os índices serão criados apenas para partições/sub-partições que você deseja.

Este recurso não é suportado para índices únicos, ou seja, para índices utilizados para impor restrições exclusivas.

Novos atributos para o comando CREATE TABLE:

• INDEXING ON (default)
• INDEXING OFF

Novos atributos para o comando CREATE INDEX:

• INDEXING FULL (default)
• INDEXING PARTIAL

Como este assunto é muito extenso e com diversas variações, irei mostrar apenas alguns exemplos de como iniciar a utilização desta nova funcionalidade.

Criando a tabela para testes:

create table tabela_teste
(
coluna1 number,
coluna2 number
)
indexing on
partition by range(coluna1)
(
partition part1 values less than(100) indexing off,
partition part2 values less than(200) indexing on,
partition part3 values less than(300),
partition part4 values less than(400)
);
Table created.

Verificando o atributo indexing:

SQL> select table_name, def_indexing

       from dba_part_tables

      where table_name='TABELA_TESTE';
 

TABLE_NAME           DEF
-------------------- ---
TABELA_TESTE         ON

Verificando o atributo indexing por partição:

SQL> select table_name, partition_name, indexing
       from dba_tab_partitions
      where table_name='TABELA_TESTE'
   order by partition_position;

TABLE_NAME           PARTITION_NAME       INDEXING

-------------------- -------------------- --------

TABELA_TESTE         PART1                 OFF

TABELA_TESTE         PART2                 ON

TABELA_TESTE         PART3                 ON

TABELA_TESTE         PART4                 ON

Alterando o atributo indexing após a tabela criada:

SQL> alter table TABELA_TESTE modify default attributes indexing off;

Table altered.


SQL>select table_name, def_indexing
      from dba_part_tables
     where table_name='TABELA_TESTE';

TABLE_NAME           DEF
-------------------- ---
TABELA_TESTE         OFF

Alterando o atributo indexing da partição após a tabela criada:

SQL> alter table TABELA_TESTE modify partition part3 indexing off;

Table altered.


SQL> select table_name, partition_name, indexing
       from dba_tab_partitions
      where table_name='TABELA_TESTE'
   order by partition_position;


TABLE_NAME           PARTITION_NAME       INDEXING

-------------------- -------------------- --------

TABELA_TESTE         PART1                OFF

TABELA_TESTE         PART2                ON

TABELA_TESTE         PART3                OFF

TABELA_TESTE         PART4                ON

Criando um índice:

SQL> create index TABELA_TESTE_INDEX01 on TABELA_TESTE(coluna1) local indexing partial;

Index created.

SQL> select index_name, partition_name, status
       from dba_ind_partitions 

      where index_name='TABELA_TESTE_INDEX01'
   order by partition_position;

INDEX_NAME           PARTITION_NAME       STATUS
-------------------- -------------------- --------

TABELA_TESTE_INDEX01 PART1                UNUSABLE

TABELA_TESTE_INDEX01 PART2                USABLE

TABELA_TESTE_INDEX01 PART3                UNUSABLE

TABELA_TESTE_INDEX01 PART4                USABLE

Efeito no plano de execução:

Referências:

  http://docs.oracle.com/database/121/VLDBG/partition.htm#VLDBG1083

Abraço,

Alex Zaballa.