Particionando uma tabela on-line com o pacote DBMS_REDEFINITION

Olá,

No artigo de Fevereiro/2011 eu apresentei uma breve introdução sobre tabelas particionadas e alguns exemplos práticos de algumas das operações básicas que podemos realizar sobre as partições das mesmas. Neste artigo irei demonstrar como poderemos particionar uma tabela (on-line) fazendo uso do pacote DBMS_REDEFINITION disponível desde a versão do Oracle 9i. Para isso irei criar duas tabelas na qual a tabela filha (alvo da particionamento), possuirá além de índices, uma Foreign key constraint para a tabela pai. Vamos então ao exemplo prático.

C:\>sqlplus scott/tiger 

SQL*Plus: Release 10.2.0.1.0 - Production on Dom Abr 3 14:08:20 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table pai (cod_grupo number constraint pk_pai primary key);

Tabela criada.

SQL> create table filho
  2   (id number constraint pk_filho primary key,
  3    cod_grupo number not null,
  4    data date not null
  5   );

Tabela criada.

SQL> create index idx_filho_codgrupo on filho (cod_grupo);

Índice criado.

SQL> alter table filho 
  2  add constraint fk_filho_pai
  3  foreign key (cod_grupo) references pai;

Tabela alterada.

SQL> select * from pai;

 COD_GRUPO
----------
        10
        20
        30

SQL> select * from filho;

        ID  COD_GRUPO DATA
---------- ---------- -------------------
         1         10 03/04/2011 14:10:19
         2         10 03/04/2011 14:10:20
         3         10 03/04/2011 14:10:21
         4         20 03/04/2011 14:10:22
         5         20 03/04/2011 14:10:23
         6         20 03/04/2011 14:10:24
         7         30 03/04/2011 14:10:25
         8         30 03/04/2011 14:10:26
         9         30 03/04/2011 14:10:27

9 linhas selecionadas.

Após a criação das tabelas, eu inseri alguns registros de teste como demonstrado acima.

SQL> select constraint_name,constraint_type 
  2    from user_constraints 
  3   where table_name='FILHO';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C004411                    C
SYS_C004412                    C
PK_FILHO                       P
FK_FILHO_PAI                   R

Acima estão listadas todas as constraints da tabela FILHO.

SQL> select index_name 
  2    from user_indexes 
  3   where table_name='FILHO';

INDEX_NAME
------------------------------
PK_FILHO
IDX_FILHO_CODGRUPO

Acima estão listadas todas os índices da tabela filho. Bom, agora irei iniciar o processo de particionamento on-line da tabela FILHO. Para isso irei conectar com o usuário SYSTEM que possui privilégios DBA.

SQL> connect system/******
Conectado.

SQL> BEGIN
  2  DBMS_REDEFINITION.CAN_REDEF_TABLE
  3   (uname=>'SCOTT',
  4    tname=>'FILHO',
  5    options_flag=>DBMS_REDEFINITION.CONS_USE_PK);
  6  END;
  7  /

Procedimento PL/SQL concluído com sucesso.

O primeiro passo executado acima foi realizado para verificar se a tabela é passível de redefinição on-line. Se a tabela não fosse uma candidata para tal, a procedure iria retornar um erro indicando o motivo de a mesma não poder ser redefinida de forma on-line. Vale a pena salientar que eu utilizei a opção CONS_USE_PK pois a mesma possui um Primary Key definida. Se não fosse o caso, poderíamos utilizar a opção CONS_USE_ROWID.

O próximo passo será criar uma tabela temporária (interim) com a mesma estrutura da tabela alvo do particionamento, só que agora particionada. Neste caso irei criar uma tabela particionada por HASH.

SQL> create table SCOTT.interim
  2   (
  3    id number,
  4    cod_grupo number,
  5    data date
  6   )
  7    partition by hash (cod_grupo)
  8     (
  9      partition ph01_filho tablespace users,
 10      partition ph02_filho tablespace users,
 11      partition ph03_filho tablespace users
 12     )
 13  enable row movement;

Tabela criada.

Percebam a falta de constraints NOT NULL na definição da tabela INTERIM. Isso é importante para evitar qualquer problema na execução dos passos seguintes.

SQL> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE
  3   (uname=>'SCOTT',
  4    orig_table=>'FILHO',
  5    int_table=>'INTERIM',
  6    options_flag=>DBMS_REDEFINITION.CONS_USE_PK);
  7  END;
  8  /

Procedimento PL/SQL concluído com sucesso.

O procedimento acima inicia o processo de redefinição on-line da tabela FILHO. Irei verificar abaixo, o que este procedimento realmente fez:

SQL> col query format a30
SQL> select mview_name,container_name,query 
  2    from dba_mviews 
  3   where owner='SCOTT';

MVIEW_NAME        CONTAINER_NAME     QUERY
----------------- ------------------ --------------------------------
INTERIM           INTERIM            SELECT "FILHO"."ID" "ID",
                                     "FILHO"."COD_GRUPO" "COD_GRUPO",
                                     "FILHO"."DATA" "DATA"
                                     FROM "SCOTT"."FILHO" "FILHO"

SQL> select master,log_table from dba_mview_logs where log_owner='SCOTT';

MASTER                         LOG_TABLE
------------------------------ ------------------------------
FILHO                          MLOG$_FILHO

Podemos perceber que uma view materializada e um log de view materializada foram automaticamente criados para dar suporte às operações DML que porventura sejam realizadas na tabela original durante a fase da redefinição on-line. Para dar continuidade ao próximo passo da redefinição on-line, executarei abaixo o procedimento para copiar os objetos dependentes para a tabela INTERIM, como constraints e índices.

SQL> var num_errors number
SQL> BEGIN
  2  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
  3   (uname=>'SCOTT',
  4    orig_table=>'FILHO',
  5    int_table=>'INTERIM',
  6    num_errors=>:num_errors);
  7  END;
  8  /

Procedimento PL/SQL concluído com sucesso.

SQL> print num_errors

NUM_ERRORS
----------
         0

Após a execução da etapa acima, podemos verificar os objetos atualmente criados no schema SCOTT.

SQL> select object_name,subobject_name,object_type
  2    from dba_objects
  3   where owner='SCOTT';

OBJECT_NAME                    SUBOBJECT_NAME                 OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PK_PAI                                                        INDEX
PAI                                                           TABLE
FILHO                                                         TABLE
PK_FILHO                                                      INDEX
IDX_FILHO_CODGRUPO                                            INDEX
INTERIM                        PH03_FILHO                     TABLE PARTITION
INTERIM                        PH02_FILHO                     TABLE PARTITION
INTERIM                        PH01_FILHO                     TABLE PARTITION
INTERIM                                                       TABLE
MLOG$_FILHO                                                   TABLE
RUPD$_FILHO                                                   TABLE
TMP$$_PK_FILHO0                                               INDEX
TMP$$_IDX_FILHO_CODGRUPO0                                     INDEX

13 linhas selecionadas.

Por fim, poderemos finalizar o processo de redefinição on-line.

SQL> BEGIN
  2  DBMS_REDEFINITION.FINISH_REDEF_TABLE
  3   (uname=>'SCOTT',
  4    orig_table=>'FILHO',
  5    int_table=>'INTERIM');
  6  END;
  7  /

Procedimento PL/SQL concluído com sucesso.
SQL> connect scott/tiger
Conectado.

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
PAI                            TABLE
INTERIM                        TABLE
FILHO                          TABLE

Neste momento não precisaremos mais da tabela temporária INTERIM. Portanto, poderemos dropá-la de forma segura.

SQL> drop table interim;

Tabela eliminada.

No mais, poderemos ver abaixo que a tabela FILHO foi particionada de forma on-line apenas utilizando o pacote DBMS_REDEFINITION.

SQL> select table_name,partitioning_type,partition_count
  2    from user_part_tables;

TABLE_NAME                     PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
FILHO                          HASH                  3

 
SQL> select constraint_name,constraint_type,status
  2    from user_constraints
  3   where table_name='FILHO';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C004411                    C ENABLED
SYS_C004412                    C ENABLED
PK_FILHO                       P ENABLED
FK_FILHO_PAI                   R ENABLED

 
SQL> select index_name,status
  2   from user_indexes
  3  where table_name='FILHO';

INDEX_NAME                     STATUS
------------------------------ --------
PK_FILHO                       VALID
IDX_FILHO_CODGRUPO             VALID