Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


segunda-feira, 2 de março de 2015

Abordando uma "New Feature" do particionamento por referência (Reference Partitioning) do Oracle 12c

Por Eduardo Legatti

Olá,

No artigo de Fevereiro/2011 fiz uma breve introdução sobre o conceito de tabelas particionadas de forma a demonstrar as operações de manutenção envolvidas nas mesmas e no artigo de Abril/2011 demonstrei como particionar uma tabela de forma on-line. Já nos artigos de Junho/2011 e Maio/2012 eu demonstrei como utilizar o Interval Partitioning e o Reference Partitioning respectivamente, ambos introduzidos no Oracle 11g.

Neste artigo, irei abordar uma inovação feita no Oracle 12c que permite utilizar o método de particionamento Reference Partitioning em uma tabela filha, tendo como pai, uma tabela particionada pelo método Interval Partitioning. No Oracle 11g isso não é possível.

Apenas para recapitular, 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. No caso do particionamento por intervalo (Interval Partitioning), o mesmo automatiza a criação de novas partições de uma tabela por uma determinada faixa ou intervalo (RANGE), de forma que o DBA não precise se preocupar em criar novas partições manualmente.

Abaixo, irei criar a tabela PEDIDO com 3 partições iniciais, tendo como coluna chave de partição, o campo DATA_PEDIDO. O tipo de particionamento utilizado será por faixa de valores (RANGE) utilizando o Interval Partitioning de forma que cada partição armazene os registros de um mês específico. Depois irei criar a tabela ITEM_PEDIDO utilizando o tipo de particionamento por referência (Reference Partitioning).

C:\>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:37:34 2015

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

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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  interval(numtoyminterval(1,'month'))
  7  store in (users)
  8  (
  9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
 10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
 11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
 12  );

Tabela criada.

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);
create table item_pedido (
*
ERRO na linha 1:
ORA-14659: O método de particionamento da tabela mãe não é suportado

Com o erro demonstrado acima, é possível perceber que com o Oracle 11g não é possível criar uma tabela particionada por referência na qual a tabela pai (referenciada), esteja particionada por intervalo (Interval Partitioning). Iremos verificar abaixo que à partir do Oracle 12c isto já será possível.

C:\>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:48:20 2015

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

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics 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  interval(numtoyminterval(1,'month'))
  7  store in (users)
  8  (
  9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
 10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
 11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
 12  );

Tabela criada.

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.  

Pronto. Como demonstrado acima, no Oracle 12c é possível criar uma tabela particionada por referência tendo como tabela referenciada, uma tabela particionada por RANGE através do Interval Partitioning.
SQL>  select table_name,
  2          partitioning_type,
  3          partition_count,
  4          interval
  5     from user_part_tables;

TABLE_NAME           PARTITION PARTITION_COUNT INTERVAL
-------------------- --------- --------------- --------------------------
PEDIDO               RANGE             1048575 NUMTOYMINTERVAL(1,'MONTH')
ITEM_PEDIDO          REFERENCE         1048575 YES 

Agora irei inserir dados nas duas tabelas de forma a demonstrar como o Oracle será capaz de criar automaticamente uma partição na tabela PEDIDO para acomodar registros com datas de um determinado mês, bem como demonstrar que a partição criada automaticamente será herdada pela tabela ITEM_PEDIDO.
 
SQL> insert into pedido
  2  select level,
  3         to_date('31/12/2014')+level,
  4         trunc(dbms_random.value(1,1000))
  5    from dual
  6  connect by level <= 120;

120 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 <= 120;

120 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_2015_01       TO_DATE(' 2015-02-01 00:00:00'         31
PEDIDO               P_2015_02       TO_DATE(' 2015-03-01 00:00:00'         28
PEDIDO               P_2015_03       TO_DATE(' 2015-04-01 00:00:00'         31
PEDIDO               SYS_P501        TO_DATE(' 2015-05-01 00:00:00'         30
ITEM_PEDIDO          P_2015_01                                              31
ITEM_PEDIDO          P_2015_02                                              28
ITEM_PEDIDO          P_2015_03                                              31
ITEM_PEDIDO          SYS_P501                                               30

8 linhas selecionadas.
  
Podemos ver pelo resultado acima que a partição SYS_P501 foi criada automaticamente na tabela PEDIDO para acomodar os registros referentes ao mês de Abril, e que essa mesma partição também foi herdada pela tabela ITEM_PEDIDO.

Google+

Nenhum comentário:

Postagens populares