Em um artigo passado, Particionando uma tabela existente utilizando DBMS_REDEFINITION vimos um breve conceito de particionamento e alguns métodos oferecidos pela Oracle (paticionamento por range,list,hash). Mas as tabelas são os únicos segmentos que podemos particionar? E os índices…

Assim como tabelas e materialized views, os índices também podem ser particionados! Mas antes de falarmos de Global e Local index, vamos definir o que é um índice.

Índices são segmentos que contém a(s) coluna(s) indexada(s) juntamente com o ROWID da linha que contém o valor indexado. Podemos dizer de forma geral que um índice permite melhorar o desempenho(tempo) da obtenção de linhas de uma instrução SQL. Veja o artigo relacionado “A importância do uso de Índices“.

Local indexes:

Índices particionados locais são mais fáceis de controlar do que outros tipos de índices particionados. Cada partição de um índice local está associada a exatamente uma partição da tabela, ou seja, eles são um reflexo das partições da tabela e seus limites (HIGH_VALUE), vejamos:

Primeiramente vou criar uma tabela particionada e popular com alguns registros.

SQL> create table exemplo_part(
  2  cod number(5),
  3  des varchar2(20)
  4  )
  5  partition by range (cod)
  6  (
  7    partition DTP100 values less than (100)
  8      tablespace USERS
  9      ,
 10    partition DTP200 values less than (200)
 11      tablespace USERS
 12      ,
 13    partition DTP300 values less than (300)
 14      tablespace USERS
 15      ,
 16    partition DTP400 values less than (400)
 17      tablespace USERS
 18  );

Table created.

SQL> insert into exemplo_part select rownum, 'REGISTRO - '||rownum from dual id connect by level < 400;

399 rows created.

SQL> commit;

Commit complete.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(
  3  ownname          => 'ANDERSON',
  4  tabname          => 'EXEMPLO_PART',
  5  estimate_percent => 100,
  6  method_opt       => 'FOR ALL COLUMNS SIZE 1',
  7  degree           => 16,
  8  granularity      => 'ALL',
  9  cascade          => TRUE);
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> col TABLE_NAME for a20
SQL> col HIGH_VALUE for a10
SQL> col PARTITION_NAME for a20
SQL> col TABLESPACE_NAME for a30
SQL> select table_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_tab_partitions
  7  where table_name='EXEMPLO_PART';

TABLE_NAME           PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------- --------------------
EXEMPLO_PART         DTP100                       99 100        USERS
EXEMPLO_PART         DTP200                      100 200        USERS
EXEMPLO_PART         DTP300                      100 300        USERS
EXEMPLO_PART         DTP400                      100 400        USERS

Agora vou criar um índice local.

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod) local;

Index created.

Observem como o índice local ficou estruturado. Mesma quantidade de partições e limites.

SQL> select index_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_ind_partitions
  7  where index_name='EXEMPLO_PART_IDX1';

INDEX_NAME                     PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------- ---------- ---------- --------------------
EXEMPLO_PART_IDX1              DTP100                       99 100        USERS
EXEMPLO_PART_IDX1              DTP200                      100 200        USERS
EXEMPLO_PART_IDX1              DTP300                      100 300        USERS
EXEMPLO_PART_IDX1              DTP400                      100 400        USERS

Veja que as partições recebem o mesmo nome das partições da tabela bem como são armazenadas nas mesmas tablespaces, mas nada impede que possamos efetuar um rename e rebuild para ajustar conforme nossa necessidade.

SQL> alter index EXEMPLO_PART_IDX1 rename partition DTP400 to ITP400;

Index altered.

SQL> alter index EXEMPLO_PART_IDX1 rebuild partition ITP400 tablespace TESTE;

Index altered.

SQL> select index_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_ind_partitions
  7  where index_name='EXEMPLO_PART_IDX1';

INDEX_NAME                     PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------- ---------- ---------- --------------------
EXEMPLO_PART_IDX1              DTP100                       99 100        USERS
EXEMPLO_PART_IDX1              DTP200                      100 200        USERS
EXEMPLO_PART_IDX1              DTP300                      100 300        USERS
EXEMPLO_PART_IDX1              ITP400                      100 400        TESTE

É possível também criar os índices locais já especificando o nome correto das partições e tablespaces:

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod) local
  2   (PARTITION ITP100 TABLESPACE TESTE,
  3    PARTITION ITP200 TABLESPACE TESTE,
  4    PARTITION ITP300 TABLESPACE TESTE,
  5    PARTITION ITP400 TABLESPACE TESTE
  6  );

Index created.

SQL> select index_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_ind_partitions
  7  where index_name='EXEMPLO_PART_IDX1';

INDEX_NAME                     PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------- ---------- ---------- --------------------
EXEMPLO_PART_IDX1              ITP100                       99 100        TESTE
EXEMPLO_PART_IDX1              ITP200                      100 200        TESTE
EXEMPLO_PART_IDX1              ITP300                      100 300        TESTE
EXEMPLO_PART_IDX1              ITP400                      100 400        TESTE

Um dos benefícios dos índices locais é que o banco de dados matem automaticamente as partições de índice em sincronia com as partições da tabela, desta forma se uma nova partição for adicionada na tabela a partição de índice é automaticamente criada, da mesma forma se uma partição da tabela for removida ela é removida do índice sem invalidar os demais, como acontece nos índices globais.

SQL> alter table exemplo_part add partition
  2  DTP500 values less than (500)
  3  tablespace USERS;

Table altered.

SQL> select table_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_tab_partitions
  7  where table_name='EXEMPLO_PART';

TABLE_NAME           PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------- --------------------
EXEMPLO_PART         DTP500                          500        USERS
EXEMPLO_PART         DTP100                       99 100        USERS
EXEMPLO_PART         DTP200                      100 200        USERS
EXEMPLO_PART         DTP300                      100 300        USERS
EXEMPLO_PART         DTP400                      100 400        USERS

SQL> select index_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_ind_partitions
  7  where index_name='EXEMPLO_PART_IDX1';

INDEX_NAME                     PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------- ---------- ---------- --------------------
EXEMPLO_PART_IDX1              DTP100                       99 100        USERS
EXEMPLO_PART_IDX1              DTP200                      100 200        USERS
EXEMPLO_PART_IDX1              DTP300                      100 300        USERS
EXEMPLO_PART_IDX1              ITP400                      100 400        TESTE
EXEMPLO_PART_IDX1              DTP500                          500        USERS

Utilizando índices locais também podemos criar índices únicos (unique index), para tanto precisamos especificar também no índice a chave da partição como neste exemplo:

SQL> create unique index EXEMPLO_PART_IDX2 on EXEMPLO_PART(cod,des) local;

Index created.

Caso a chave da partição não seja especificada no unique index o erro abaixo será apresentado.

SQL> create unique index EXEMPLO_PART_IDX3 on EXEMPLO_PART(des) local;
create unique index EXEMPLO_PART_IDX3 on EXEMPLO_PART(des) local
                                         *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

Global indexes:

Índices globais podem ser de dois tipos: particionados ou não-particionados. Quando particionados não precisam necessariamente refletir a mesma quantidade de partições de sua tabela e podem ser particionados por range(intervalo) ou hash(faixa). Vejamos:

--RANGE:

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod)
  2  global partition by range(cod)
  3   (PARTITION ITP250 VALUES LESS THAN (250) TABLESPACE users,
  4    PARTITION ITP500 VALUES LESS THAN (500) TABLESPACE users,
  5    PARTITION ITPMAX VALUES LESS THAN (MAXVALUE) TABLESPACE users
  6  );

Index created.

SQL> select index_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_ind_partitions
  7  where index_name='EXEMPLO_PART_IDX1';

INDEX_NAME                     PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------- ---------- ---------- --------------------
EXEMPLO_PART_IDX1              ITP250                      249 250        USERS
EXEMPLO_PART_IDX1              ITP500                      150 500        USERS
EXEMPLO_PART_IDX1              ITPMAX                        0 MAXVALUE   USERS

--HASH:

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod)
  2  global partition by hash(cod)
  3   (PARTITION ITP1 TABLESPACE users,
  4    PARTITION ITP2 TABLESPACE users
  5  );

Index created.

SQL> select index_name,
  2  partition_name,
  3  num_rows,
  4  high_value,
  5  tablespace_name
  6  from dba_ind_partitions
  7  where index_name='EXEMPLO_PART_IDX1';

INDEX_NAME                     PARTITION_NAME         NUM_ROWS HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------- ---------- ---------- --------------------
EXEMPLO_PART_IDX1              ITP1                        197            USERS
EXEMPLO_PART_IDX1              ITP2                        202            USERS

Observem a imagem abaixo ilustrando um exemplo de índice global particonado.

Utilizando índices globais particionados precisamos nos atentar que dependendo das operações DDL executadas sobre a tabela (ADD, DROP, MOVE, TRUNCATE, SPLIT, …) podemos invalidar o índice, deste modo, podemos sempre utilizar a cláusula UPDATE GLOBAL INDEXES para não invalidá-los.

Outro detalhe é que ao criar índices globais por range sempre precisamos especificar uma partição com o limite MAXVALUE, caso contrário um erro ORA-14021 será gerado.

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod)
  2  global partition by range(cod)
  3   (PARTITION ITP250 VALUES LESS THAN (250) TABLESPACE users,
  4    PARTITION ITP500 VALUES LESS THAN (500) TABLESPACE users
  5  );
)
*
ERROR at line 5:
ORA-14021: MAXVALUE must be specified for all columns

Já os índices globais não-particionados são exatamente iguais aos índices regulares (Btree), deste modo são criados utilizando a mesma syntaxe:

SQL> create index EXEMPLO_PART_IDX1 on EXEMPLO_PART(cod);

Index created.

Referência:
http://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm
http://docs.oracle.com/cd/E18283_01/server.112/e16541/partition.htm

Autor: Anderson Graf

Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI

Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata

Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br

• 
• 
• 
• 
•