Um pouco sobre índices particionados no Oracle ...

Olá,

Em artigos anteriores como os de Fevereiro/2011, Abril/2011 e Junho/2011 apresentei uma visão geral sobre tabelas particionadas. A idéia deste artigo é demonstrar os tipos de índices que podemos criar sobre as tabelas particionadas, como os índices particionados globais e locais, além dos índices globais não particionados.

Bom, de forma geral, o objetivo de um índice é permitir acesso mais rápido às linhas de uma tabela. Um índice armazena o valor da coluna ou colunas que estão sendo indexadas, junto com o ROWID da linha que contém o valor indexado. A figura abaixo representa um índice comum ou regular (Btree) criado sobre uma tabela não particionada.

Em se tratando de tabelas particionadas, podemos criar índices locais e globais, ambos particionados e também poderemos criar índices globais não particionados. Para este artigo criarei a tabela T1 particionada por faixa (RANGE) como demonstrado abaixo:

SQL> create table t1
  2    (id  number,
  3     cod number)
  3     tablespace users
  4     partition by range(id)
  5      (
  6       partition pdezenas  values less than (100)  tablespace tbs_dezenas,
  7       partition pcentenas values less than (1000) tablespace tbs_centenas,
  8       partition pmilhares_1000 values less than (2000)  tablespace tbs_milhares,
  9       partition pmilhares_2000 values less than (3000)  tablespace tbs_milhares,
 10       partition pmilhares_3000 values less than (4000)  tablespace tbs_milhares,
 11       partition pmilhares_4000 values less than (5000)  tablespace tbs_milhares,
 12       partition pmilhares_n values less than (maxvalue) tablespace tbs_milhares
 13    );

Tabela criada.

Bom, os índices particionados locais refletem a estrutura da tabela particionada e são particionados de forma igual a tabela particionada subjacente, ou seja, eles são particionados nas mesmas colunas que a tabela e, portanto, tem os mesmos números de partições e os mesmos limites (HIGH_VALUE) que a tabela particionada. Vale a pena salientar que as partições de índices recebem o mesmo nome das partições de tabela. Um dos benefícios dos índice locais é a sua afinidade com a tabela subjacente, pois quando uma nova partição é criada, a partição de índice correspondente é criada automaticamente. Da mesma maneira, ao dropar uma partição da tabela, a partição de índice também é dropada automaticamente sem invalidar quaisquer outras partições de índice, como seria no caso de um índice global. A figura abaixo representa um índice particionado localmente.

SQL> create index idx_t1_local on t1 (id) local;

Índice criado.

SQL> select index_name,
  2         partition_name,
  3         high_value,
  4         tablespace_name
  5    from user_ind_partitions;

INDEX_NAME         PARTITION_NAME           HIGH_VALUE TABLESPACE_NAME
------------------ ------------------------ ---------- ------------------------
IDX_T1_LOCAL       PDEZENAS                 100        TBS_DEZENAS
IDX_T1_LOCAL       PCENTENAS                1000       TBS_CENTENAS
IDX_T1_LOCAL       PMILHARES_1000           2000       TBS_MILHARES
IDX_T1_LOCAL       PMILHARES_2000           3000       TBS_MILHARES
IDX_T1_LOCAL       PMILHARES_3000           4000       TBS_MILHARES
IDX_T1_LOCAL       PMILHARES_4000           5000       TBS_MILHARES
IDX_T1_LOCAL       PMILHARES_N              MAXVALUE   TBS_MILHARES

Podemos também criar índices únicos locais, mas para isso a coluna chave de partição precisa obrigatoriamente fazer parte do índice, diferentemente dos índices globais não particionados. Se tentarmos criar um índice único local sem a coluna chave da partição, então o erro ORA-14039 será emitido:

SQL> create unique index idx_t1_local on t1 (cod) local;
create unique index idx_t1_local on t1 (cod) local 

*ERRO na linha 1:ORA-14039: colunas particionadas devem formar um subconjunto de
colunas de chaves de um índice UNIQUE

Abaixo podemos ver que o índice será criado somente se a coluna chave da partição estiver presente:

SQL> create unique index idx_t1_local on t1 (id,cod) local;

Índice criado.

Já os índices particionados globais não precisam ser necessariamente particionados da mesma maneira que a tabela subjacente. A figura abaixo mostra claramente que o número de partições na tabela pode ou não, ser igual ao número de partições do índice.

Dependendo das operações DDL executadas nas partições da tabela (ADD, DROP, MOVE, TRUNCATE, SPLIT, MERGE, EXCHANGE ...) um índice global poderá ficar marcado como inutilizável (unusable). Portanto, utilizar a cláusula UPDATE GLOBAL INDEXES é uma boa prática:

SQL> ALTER TABLE ... DROP PARTITION P1 UPDATE GLOBAL INDEXES;
SQL> TRUNCATE TABLE ... UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE ... DROP PARTITION P1 UPDATE GLOBAL INDEXES;

No caso de índices globais, para que o mesmo seja criado, ele deverá ser prefixado, ou seja, a coluna chave de partição precisará fazer parte do índice, caso contrário o erro ORA-14038: índice GLOBAL particionado deve ser prefixado será emitido. É importante frisar também que no caso de índices globais, criar uma partição com o limite (MAXVALUE) é obrigatória, caso contrário o erro ORA-14021 será emitido como demonstrado abaixo:

SQL> create index idx_t1_global on t1 (id)
  2  tablespace users
  3  global partition by range (id)
  4  (partition idxp_1500 values less than(1500),
  5   partition idxp_2900 values less than(2900),
  6   partition idxp_4600 values less than(4600),
  7   partition idxp_5000 values less than(5000));
 partition idxp_5000 values less than(5000))
*ERRO na linha 6:ORA-14021: MAXVALUE deve ser especificado para todas as colunas

SQL> create index idx_t1_global on t1 (id)
  2  tablespace users
  3  global partition by range (id)
  4  (partition idxp_1500 values less than(1500),
  5   partition idxp_2900 values less than(2900),
  6   partition idxp_4600 values less than(4600),
  7   partition idxp_5000 values less than(5000),
  8   partition idxp_n values less than(maxvalue));

Índice criado.

SQL> select index_name,
  2         partition_name,
  3         high_value,
  4         tablespace_name
  5    from user_ind_partitions;

INDEX_NAME         PARTITION_NAME           HIGH_VALUE TABLESPACE_NAME
------------------ ------------------------ ----------- -----------------------
IDX_T1_GLOBAL      IDXP_1500                1500       USERS
IDX_T1_GLOBAL      IDXP_2900                2900       USERS
IDX_T1_GLOBAL      IDXP_4600                4600       USERS
IDX_T1_GLOBAL      IDXP_5000                5000       USERS
IDX_T1_GLOBAL      IDXP_N                   MAXVALUE   USERS

A partir do Oracle 10g podemos também criar índices globais particionados por HASH como demonstrado abaixo:

SQL> create index idx_t1_global_hash on t1 (id)
  2  tablespace users
  3  global partition by hash (id)
  4  (partition idxp1,
  5   partition idxp2,
  6   partition idxp3,
  7   partition idxp4,
  8   partition idxp5);

Índice criado.

SQL> select index_name,
  2         partition_name,
  3         high_value,
  4         tablespace_name
  5    from user_ind_partitions;

INDEX_NAME         PARTITION_NAME           HIGH_VALUE TABLESPACE_NAME
------------------ ------------------------ ---------- ------------------------
IDX_T1_GLOBAL_HASH IDXP1                               USERS
IDX_T1_GLOBAL_HASH IDXP2                               USERS
IDX_T1_GLOBAL_HASH IDXP3                               USERS
IDX_T1_GLOBAL_HASH IDXP4                               USERS
IDX_T1_GLOBAL_HASH IDXP5                               USERS

Podemos notar que os índices globais não particionados são exatamente iguais aos índices regulares criados em uma tabela não particionada e, portanto, a sintaxe para a criação do índice é a mesma. A figura abaixo mostra o relacionamento entre um índice global não particionado e uma tabela particionada:

Por fim, vale a pena salientar que podemos criar também índices de bitmap em tabelas particionadas. A única restrição é que os mesmos só podem ser índice locais.