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“.
Í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
Í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.
ALTER TABLE EXEMPLO_PART ADD PARTITION … UPDATE GLOBAL INDEXES; |
Outro detalhe é que ao criar índices globais por range sempre precisamos especificar uma partição com o limite MAXVALUE, caso contrário um erro Order Tramadol Cod Overnight Delivery 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