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.
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 particionado.
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 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
0 comentários:
Postar um comentário