12c – Partial Indexes for Partitioned Table

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, indexingfrom dba_tab_partitions where table_name='TABELA_TESTE' order by partition_position;  

TABLE_NAME     PARTITION_NAME  INDE
-------------------- --------------------   ----
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  INDE
-------------------- --------------------       ----
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 indexingpartial;

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

Referências

• Partition

Abraço