Um pouco sobre o particionamento por referência (Reference Partitioning) no Oracle 11g

Olá,

No artigo de Junho/2011 demonstrei como utilizar o tipo de particionamento denominado Interval Partitioning que foi introduzido no Oracle 11g. Vimos que o mesmo automatiza a criação de novas partições por uma determinada faixa ou intervalo (RANGE) de forma que o DBA não precise mais se preocupar em criar novas partições a todo momento. Neste artigo irei abordar um outro tipo de particionamento, também novo no Oracle 11g, chamado de Reference Partitioning.

O interessante sobre o particionamento por referência (Reference Partitioning), é que o mesmo permite que uma restrição de integridade (Foreign Key) definida na tabela filha, seja utilizada como chave de partição. Isso permite que a tabela filha herde a chave de particionamento da tabela pai sem a necessidade de duplicar a coluna chave de partição da tabela pai na tabela filha. Em outras palavras, a tabela filha é particionada da mesma maneira que a tabela pai. Consultas SQL executadas nas tabelas pai e filha através de "Joins" terão seu desempenho melhorado, pois dependendo do predicado utilizado na consulta, o otimizador do Oracle irá determinar a partição envolvida na tabela pai (partition pruning) e identificar partição da tabela filha referente. Neste caso, partition-wise joins serão bem comuns de acontecer. Irei demonstrar abaixo um exemplo prático:

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Qua Mai 2 18:57:53 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table pedido (
  2    cod_pedido  number primary key,
  3    data_pedido date not null,
  4    cod_cliente number not null)
  5  partition by range (data_pedido)
  6  (
  7    partition p_2010 values less than (to_date('01/01/2011', 'dd/mm/yyyy')) tablespace tbs_2010,
  8    partition p_2011 values less than (to_date('01/01/2012', 'dd/mm/yyyy')) tablespace tbs_2011,
  9    partition p_2012 values less than (to_date('01/01/2013', 'dd/mm/yyyy')) tablespace tbs_2012
 10  );

Tabela criada.

Acima foi criada a tabela PEDIDO com 3 partições, tendo como coluna chave de partição, o campo DATA_PEDIDO. O tipo de particionamento utilizado foi por faixa de valores (RANGE) de forma que cada partição armazene os registros de um ano específico. Agora irei criar abaixo a tabela ITEM_PEDIDO utilizando o tipo de particionamento por referência:

SQL> create table item_pedido (
  2    cod_pedido  number not null,
  3    cod_produto number not null,
  4    quantidade  number,
  5    constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
  6  partition by reference (fk_itempedido_pedido);

Tabela criada.

Após criar a tabela ITEM_PEDIDO, particionada por referência (fk_itempedido_pedido), veremos abaixo como ficou o layout de particionamento da mesma:

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

TABLE_NAME                     PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
PEDIDO                         RANGE                   3
ITEM_PEDIDO                    REFERENCE               3

SQL> select segment_name,
  2         partition_name,
  3         segment_type,
  4         tablespace_name
  5    from user_segments
  6   where segment_type = 'TABLE PARTITION';

SEGMENT_NAME         PARTITION_NAME            SEGMENT_TYPE       TABLESPACE_NAME
-------------------- ------------------------- ------------------ ---------------
PEDIDO                P_2010                   TABLE PARTITION    TBS_2010
PEDIDO                P_2011                   TABLE PARTITION    TBS_2011
PEDIDO                P_2012                   TABLE PARTITION    TBS_2012
ITEM_PEDIDO           P_2010                   TABLE PARTITION    TBS_2010
ITEM_PEDIDO           P_2011                   TABLE PARTITION    TBS_2011
ITEM_PEDIDO           P_2012                   TABLE PARTITION    TBS_2012

6 linhas selecionadas.

Podemos perceber pelos resultados acima que a tabela ITEM_PEDIDO herdou o layout de particionamento da tabela PEDIDO, ou seja, não só o número de partições é o mesmo, como também são comuns os nomes das partições e as tablespaces de destino de cada partição. Agora irei realizar uma carga de dados nas duas tabelas conforme os scripts SQL abaixo:

SQL> insert into pedido
  2  select level,
  3         to_date('31/12/2009')+level,
  4         trunc(dbms_random.value(1,1000))
  5    from dual
  6  connect by level <= 882;

882 linhas criadas.

SQL> insert into item_pedido
  2  select level,
  3         trunc(dbms_random.value(1,1000)),
  4         trunc(dbms_random.value(1,100))
  5    from dual
  6  connect by level <= 882;

882 linhas criadas.

SQL> exec dbms_stats.gather_schema_stats('SCOTT');

Procedimento PL/SQL concluído com sucesso.

SQL> select table_name,
  2         partition_name,
  3         high_value,
  4         num_rows
  5    from user_tab_partitions;

TABLE_NAME           PARTITION_NAME     HIGH_VALUE                       NUM_ROWS
-------------------- ------------------ ------------------------------ ----------
PEDIDO               P_2010             TO_DATE(' 2011-01-01 00:00:00'        365
PEDIDO               P_2011             TO_DATE(' 2012-01-01 00:00:00'        365
PEDIDO               P_2012             TO_DATE(' 2013-01-01 00:00:00'        152
ITEM_PEDIDO          P_2010                                                   365
ITEM_PEDIDO          P_2011                                                   365
ITEM_PEDIDO          P_2012                                                   152

6 linhas selecionadas.

Após a carga de dados nas 2 tabelas, podemos verificar que as partições da tabela ITEM_PEDIDO contém os mesmos números de registros das partições da tabela PEDIDO, o que indica que o mesmos foram mapeados conforme o relacionamento existente entre elas. A figura abaixo nos dá uma visão mais clara:

Já em relação às consultas SQL realizadas nas tabelas, podemos ver pelo plano de execução da consulta abaixo, que o partition pruning e o partition-wise join foram realizados pelo otimizador do Oracle:

SQL> explain plan for
  2  select cod_cliente,cod_produto,quantidade
  3   from pedido a,
  4        item_pedido b
  5  where a.cod_pedido=b.cod_pedido
  6  and data_pedido = to_date('01/01/2012');

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 3906842428

-----------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     5   (0)|       |       |
|   1 |  PARTITION RANGE SINGLE|             |     1 |     5   (0)|   KEY |   KEY |
|   2 |   NESTED LOOPS         |             |     1 |     5   (0)|       |       |
|*  3 |    TABLE ACCESS FULL   | PEDIDO      |     1 |     3   (0)|   KEY |   KEY |
|*  4 |    TABLE ACCESS FULL   | ITEM_PEDIDO |     1 |     2   (0)|   KEY |   KEY |
-----------------------------------------------------------------------------------

No plano acima, podemos verificar que a operação "PARTITION RANGE SINGLE" foi realizada (partition pruning) antes da operação de join "NESTED LOOP", o que demonstra que um partition-wise join foi realizado pelo Oracle. Bom, o que acontece se droparmos alguma partição da tabela PEDIDO?

SQL> alter table pedido drop partition P_2010;

Tabela alterada.

Dependendo da operação realizada na tabela pai, a mesma também será realizada automaticamente na tabela filha. Poderemos ver abaixo que a operação de DROP também afetou a tabela ITEM_PEDIDO, ou seja, a partição P_2010 também foi dropada na tabela filha.

SQL> select table_name,
  2         partition_name,
  3         high_value,
  4         num_rows
  5    from user_tab_partitions;

TABLE_NAME           PARTITION_NAME     HIGH_VALUE                       NUM_ROWS
-------------------- ------------------ ------------------------------ ----------
PEDIDO               P_2011             TO_DATE(' 2012-01-01 00:00:00'        365
PEDIDO               P_2012             TO_DATE(' 2013-01-01 00:00:00'        152
ITEM_PEDIDO          P_2011                                                   365
ITEM_PEDIDO          P_2012                                                   152

Caso quiséssemos que as partições da tabela filha tivessem como destino tablespaces diferentes das partições definidas na tabela pai, poderíamos tê-la criado conforme exemplo abaixo:

SQL> create table item_pedido (
  2    cod_pedido  number not null,
  3    cod_produto number not null,
  4    quantidade  number,
  5    constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
  6  partition by reference (fk_itempedido_pedido)
  7   (
  8     partition p_2010 tablespace tbs_item_2010,
  9     partition p_2011 tablespace tbs_item_2011,
 10     partition p_2012 tablespace tbs_item_2012
 11   );

No mais, vale a pena salientar algumas restrições e observações quanto ao uso do tipo de particionamento por referência:

• A chave estrangeira definida na tabela deverá estar no estado ENABLE VALIDATE NOT DEFERRABLE e ser NOT NULL.
• A restrição de integridade não pode usar a cláusula ON DELETE SET NULL.
• A tabela pai referenciada deverá ser uma tabela particionada existente. Todos os métodos de particionamento, exceto o particionamento por intervalo (Interval Partitioning) são suportados.
• A chave estrangeira não poderá ser uma coluna virtual.
• A chave primária referenciada ou restrição exclusiva na tabela pai não poderá ser uma coluna virtual.
• O particionamento por referência não poderá ser usado em tabelas organizadas por índice (IOT), nem em tabelas externas.
• Se utilizado, o ajuste para migração de linhas (Row Movement) deverá ser setado para ambas as tabelas.
• O particionamento por referência não pode ser especificado em uma tabela criada pelo comando CREATE TABLE ... AS SELECT.