sexta-feira, 12 de abril de 2013

Particionando uma tabela existente utilizando DBMS_REDEFINITION

O Particionamento é uma solução oferecida na versão Enterprise Edition(EE) – With Partitioning, mediante licenciamento, que permite particionarmos tabelas e índices em pedaços menores simplificando sua administração e melhorando o desempenho de consultas. 

A Oracle oferece alguns métodos de particionamento, como podemos verificar abaixo: 

Range Partitioning: Particionamento por intervalos. É o tipo mais comum de particionamento e normalmente utilizado sobre datas; 

List Partitioning: Particionamento em listas, onde podemos especificar uma lista de valores atribuidos à cada partição; 

Hash Partitioning: Particionamento em faixas, normalmente dados que não encaixam no formato de particionamento de listas ou ranges; 

Composite Partitioning: Particionamento composto por partições range e sub-partições (list ou hash). 

Neste artigo irei demonstrar como podemos particionar uma tabela já existente utilizando o modelo range(intervalo) através da package DBMS_REDEFINITION existente desde a versão 9i e que permite uma redefinição ONLINE de tabelas. 

Primeiramente vamos criar e popular duas simples tabelas contendo constraints e índices.

SQL> create table forma_pagamento (
  2  codigo number(2) constraint pk_codigo primary key,
  3  descricao varchar2(50)
  4  );
 
Table created.
 
insert into forma_pagamento values (1,'A VISTA');
insert into forma_pagamento values (2,'BOLETO BANCARIO');
insert into forma_pagamento values (3,'TRANSFERENCIA BANCARIA');
commit;
 
SQL> select * from forma_pagamento;
 
    CODIGO DESCRICAO
---------- --------------------------------------------------
         1 A VISTA
         2 BOLETO BANCARIO
         3 TRANSFERENCIA BANCARIA
 
SQL> create table venda (
  2  id number(7) constraint pk_id primary key,
  3  codigo_pag number(2),
  4  data_venda date,
  5  constraint fk_venda_forma_pag foreign key (codigo_pag) references forma_pagamento(codigo)
  6  );
 
Table created.
 
SQL> create index venda_idx1 on venda(codigo_pag);
 
Index created.
 
SQL> create index venda_idx2 on venda(codigo_pag,data_venda);
 
Index created.
 
SQL> DECLARE
  2    wk_codigo_pag    forma_pagamento.codigo%TYPE;
  3    wk_data_venda  DATE;
  4  BEGIN
  5    FOR i IN 1 .. 1000000 LOOP
  6
  7      SELECT ROUND(DBMS_RANDOM.VALUE(1,3)) INTO wk_codigo_pag FROM DUAL;
  8
  9      IF MOD(i, 3) = 0 THEN
 10        wk_data_venda := ADD_MONTHS(SYSDATE, -24);
 11      ELSIF MOD(i, 2) = 0 THEN
 12        wk_data_venda := ADD_MONTHS(SYSDATE, -12);
 13      ELSE
 14        wk_data_venda := SYSDATE;
 15      END IF;
 16
 17      INSERT INTO VENDA (id, codigo_pag, data_venda)
 18      VALUES (i, wk_codigo_pag, wk_data_venda);
 19
 20    END LOOP;
 21    COMMIT;
 22  END;
 23  /
 
PL/SQL procedure successfully completed.
 
SQL> commit;
 
Commit complete.

Criado e populado as tabelas vamos verificar as constraints e índices da tabela VENDA que será particionada posteriormente.

SQL> select constraint_name,constraint_type
  2  from user_constraints
  3  where table_name='VENDA';
 
CONSTRAINT_NAME                C
------------------------------ -
PK_ID                          P
FK_VENDA_FORMA_PAG             R
 
SQL> select index_name
  2  from dba_indexes
  3  where table_name='VENDA';
 
INDEX_NAME
------------------------------
VENDA_IDX2
VENDA_IDX1
PK_ID

Montado nosso cenário, vamos iniciar o processo de particionamento ONLINE da tabela VENDA (DATA_VENDA) por range(intervalo) utilizando a DBMS_REDEFINITION. 

Nosso primeiro passo é verificar se a tabela pode ser redefinida de forma online. Caso não seja uma candidata, a procedure ira retornar um erro indicando o motivo.

SQL> BEGIN
  2  DBMS_REDEFINITION.CAN_REDEF_TABLE(uname => 'ANDERSON', tname => 'VENDA');
  3  END;
  4  /
 
PL/SQL procedure successfully completed.

Neste exemplo não estou especificando a opção OPTIONS_FLAG, desta forma a procedure CAN_REDEF_TABLE utiliza a opção default DBMS_REDEFINITION.CONS_USE_PK que realiza a redefinição usando a chave-primária da tabela (condição existente na nossa tabela). Caso a tabela não possua PK, podemos especificar OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_ROWID 

Agora iremos criar nossa tabela temporária, conhecida por INTERIM, utilizando a mesma estrutura da tabela anterior (VENDA) porem agora particionada e sem constraints/índices.

SQL> create table venda_interim (
  2  id number(7),
  3  codigo_pag number(2),
  4  data_venda date
  5  )
  6  partition by range (DATA_VENDA)
  7  (
  8     partition PRANGE2011 values less than (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  9       tablespace USERS
 10       ,
 11     partition PRANGE2012 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 12       tablespace USERS
 13       ,
 14     partition PRANGE2013 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 16       tablespace USERS
 15  )
 17  tablespace USERS;
 
Table created.

O próximo passo é iniciar o processo de redefinição online da tabela, vejamos:

SQL> BEGIN
  2  DBMS_REDEFINITION.start_redef_table(
  3    uname      => 'ANDERSON',
  4    orig_table => 'VENDA',
  5    int_table  => 'VENDA_INTERIM');
  6  END;
  7  /
 
PL/SQL procedure successfully completed.

Dando sequencia iremos realizar a cópia dos objetos dependentes para a tabela INTERIM, como grants, triggers, constraints, índices e privilégios.

SQL> var num_errors number
SQL> BEGIN
  2    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname      => 'ANDERSON',
  3                                            orig_table => 'VENDA',
  4                                            int_table  => 'VENDA_INTERIM',
  5                                            num_errors => :num_errors);
  6  END;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> print num_errors
 
NUM_ERRORS
----------
         0

Vamos disparar um sincronismo para manter a tabela INTERIM sincronizada com a tabela original(VENDA). No nosso cenário não temos nenhum benefício, pois ninguém esta alterando ou inserindo registros na tabela VENDA, contudo em um ambiente OLTP cuja tabela esta sofrendo constantes operações DML o sincronismo é útil para minimizar a quantidade de sincronização necessária a ser feita pelo procedimento FINISH_REDEF_TABLE(abaixo) acelerando as operações subsequentes.

SQL> BEGIN
  2  DBMS_REDEFINITION.sync_interim_table(
  3    uname      => 'ANDERSON',
  4    orig_table => 'VENDA',
  5    int_table  => 'VENDA_INTERIM');
  6  END;
  7  /
 
PL/SQL procedure successfully completed.

Por fim, disparamos uma coleta de estatística sobre a VENDA_INTERIM e finalizamos o processo de redefinição.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'ANDERSON',
  3                                TABNAME          => 'VENDA_INTERIM',
  4                                estimate_percent => 100,
  5                                method_opt       => 'FOR ALL COLUMNS SIZE 1',
  6                                degree           => 16,
  7                                granularity      => 'ALL',
  8                                cascade          => TRUE);
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> BEGIN
  2  DBMS_REDEFINITION.finish_redef_table(
  3    uname      => 'ANDERSON',
  4    orig_table => 'VENDA',
  5    int_table  => 'VENDA_INTERIM');
  6  END;
  7 /
 
PL/SQL procedure successfully completed.

Agora já podemos remover a tabela INTERIM(VENDA_INTERIM)

SQL> drop table venda_interim;
 
Table dropped.

Vamos verificar nossas constrains, índices e os particionamentos da tabela.

SQL> select constraint_name,constraint_type, status
  2  from user_constraints
  3  where table_name='VENDA';
 
CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_ID                          P ENABLED
FK_VENDA_FORMA_PAG             R ENABLED
 
SQL> select index_name,status
  2  from user_indexes
  3  where table_name='VENDA';
 
INDEX_NAME                     STATUS
------------------------------ --------
PK_ID                          VALID
VENDA_IDX1                     VALID
VENDA_IDX2                     VALID
 
SQL> select table_name, partition_name, num_rows
  2  from dba_tab_partitions
  3  where table_name='VENDA';
 
TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
VENDA                          PRANGE2011                         333333
VENDA                          PRANGE2012                         333334
VENDA                          PRANGE2013                         333333

Em um próximo artigo será abordado métodos de particionamento e também particionamento de índices(LOCAL, GLOBAL..), fique ligado!

FONTES:
http://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm#i460895 http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_redefi.htm
Postagem mais recente Postagem mais antiga Página inicial

0 comentários:

Postar um comentário

Translate

# ACE Program

#Oracle

#Oracle
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

#Blog reconhecido

#ARTICULISTA

Marcadores

Postagens populares