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.
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.
Nenhum comentário:
Postar um comentário