Interval Partitioning. Abordando um conceito de particionamento por intervalo do Oracle 11g
Neste artigo irei demonstrar um novo conceito de particionamento de tabelas introduzido no Oracle 11g que 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 tempo. O Oracle 11g se encarregará desta tarefa de forma automática de acordo com a demanda. Vale a pena salientar que o Interval Partitioning nada mais é que um Range Partitioning aprimorado. Para fixar o conceito, vamos a um exemplo prático:
C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Qua Jun 8 20:00:18 2011
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 t1
2 (id number)
3 tablespace users
4 partition by range(id)
5 interval (100)
6 (
7 partition p1 values less than (1000)
8 );
Tabela criada.Acima, eu criei uma tabela particionada com apenas uma partição (P1) que armazenará registros com ID até 999. Perceberam a palavra-chave INTERVAL na linha 5:
SQL> select table_name,
2 partitioning_type,
3 partition_count,
4 def_tablespace_name,
5 interval
6 from user_part_tables
7 where table_name='T1';
TABLE_NAME PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME INTERVAL
----------- --------- --------------- ------------------- --------
T1 RANGE 1048575 USERS 100Pois bem, as linhas inseridas com um ID até 999 residirão na partição P1 da tabela T1. As linhas inseridas com um ID maior que 999 acionarão a criação de uma nova partição com uma faixa ou intervalo de 100 cada, ou seja, o limite superior de cada nova partição sempre terá como base o valor do limite superior da partição mais alta mais 100.
Observaram a coluna PARTITION_COUNT da view USER_PART_TABLES no resultado do SQL acima? No Oracle, o número máximo de partições é limitada em 1024K-1, o que dá 1048575 partições. Para um melhor entendimento, veja o exemplo abaixo:
SQL> insert into t1 select level from dual connect by level <= 1405;
1405 linhas criadas.
SQL> commit;
Commit concluído.
SQL> analyze table t1 compute statistics;
Tabela analisada.
SQL> select table_name,
2 partition_name,
3 high_value,
4 num_rows
5 from user_tab_partitions
6 where table_name='T1';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
----------- --------------- ----------- --------
T1 P1 1000 999 (1-999)
T1 SYS_P25 1100 100 (1000-1999)
T1 SYS_P26 1200 100 (1100-1999)
T1 SYS_P27 1300 100 (1200-1299)
T1 SYS_P28 1400 100 (1300-1399)
T1 SYS_P29 1500 6 (1400-1405)
6 linhas selecionadas.Após a inserção de 1405 registros, podemos perceber que o Oracle criou, de forma automática, 5 partições afim de acomodar as novas linhas dentro da faixa especificada que foi de 100 em 100.
Bom, e se após algum tempo eu precisar ou quiser alterar este intervalo para novas linhas? Sem problemas. Veja o comando abaixo:
SQL> alter table t1 set INTERVAL (5000);
Tabela alterada.
SQL> select table_name,
2 partitioning_type,
3 partition_count,
4 def_tablespace_name,
5 interval
6 from user_part_tables
7 where table_name='T1';
TABLE_NAME PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME INTERVAL
----------- --------- --------------- ------------------- --------
T1 RANGE 1048575 USERS 5000Perceberam que o intervalo agora é de 5000? Agora irei inserir novas linhas através do SQL abaixo:
SQL> insert into t1
2 select id from (select level id
3 from dual
4 connect by level <= 20000)
5 where id >= 1406;
18595 linhas criadas.
SQL> analyze table t1 compute statistics;
Tabela analisada.
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
----------- --------------- ----------- --------
T1 P1 1000 999 (1-999)
T1 SYS_P25 1100 100 (1000-1999)
T1 SYS_P26 1200 100 (1100-1999)
T1 SYS_P27 1300 100 (1200-1299)
T1 SYS_P28 1400 100 (1300-1399)
T1 SYS_P29 1500 100 (1400-1499)
T1 SYS_P30 6500 5000 (1500-6499)
T1 SYS_P31 11500 5000 (6500-11499)
T1 SYS_P32 16500 5000 (11500-16499)
T1 SYS_P33 21500 3501 (16500-20000)
10 linhas selecionadas.Podemos perceber que 4 novas partições foram criadas afim de acomodar as novas linhas dentro do intervalo proposto. E se quisermos criar uma tabela particionada tendo como chave da partição uma coluna do tipo DATE na qual os registros fiquem acomodados em partições mensais? Veja o exemplo abaixo:
SQL> create table t2
2 (data date)
3 tablespace users
4 partition by range(data)
5 interval(numtoyminterval(1,'month'))
6 store in (tbs01,tbs02,tbs03)
7 (
8 partition p1 values less than (to_date('01/01/2011','dd/mm/yyyy'))
9 );
Tabela criada.
SQL> select table_name,
2 partitioning_type,
3 partition_count,
4 interval
5 from user_part_tables
6 where table_name='T2';
TABLE_NAME PARTITION PARTITION_COUNT INTERVAL
----------- --------- --------------- --------------------------
T2 RANGE 1048575 NUMTOYMINTERVAL(1,'MONTH')Acima, eu criei uma tabela particionada que irá armazenar os registros de forma mensal tendo como limite superior inicial a data 01/01/2011, ou seja, todos os registros com data inferior a 2011 serão armazenados na partição P1. Vale a pena salientar que os registros da partição P1 serão armazenados na tablespace USERS e que os registros das novas partições que forem criadas deverão ser armazenados nas tablespaces TBS01, TBS02 e TBS03. O Oracle irá utilizará um algoritmo para balancear os registros nessas 3 tablespaces. Abaixo irei inserir alguns registros com data de 01/01/2010 em diante de forma a popular a tabela.
SQL> insert into t2
2 select to_date('31/12/2009')+level from dual
3 connect by level <= 486;
486 linhas criadas.
SQL> analyze table t2 compute statistics;
Tabela analisada.
SQL> select table_name,
2 partition_name,
3 high_value,
4 num_rows
5 from user_tab_partitions
6 where table_name='T2';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
----------- --------------- ------------------------------- --------
T2 P1 TO_DATE(' 2011-01-01 00:00:00' 365 (2010)
T2 SYS_P61 TO_DATE(' 2011-02-01 00:00:00' 31 (Jan/2011)
T2 SYS_P62 TO_DATE(' 2011-03-01 00:00:00' 28 (Fev/2011)
T2 SYS_P63 TO_DATE(' 2011-04-01 00:00:00' 31 (Mar/2011)
T2 SYS_P64 TO_DATE(' 2011-05-01 00:00:00' 30 (Abr/2011)
T2 SYS_P65 TO_DATE(' 2011-06-01 00:00:00' 1 (Mai/2011)Perceberam como os registros foram acomodados? Todos os registros com data até 31/12/2010 foram armazenados na partição P1. Para acomodar os demais registros com data superior a 31/12/2010, o Oracle criou automaticamente 5 novas partições com intervalo mensal.
Se por acaso quisermos alterar o intervalo para que novos registros fiquem acomodados de forma trimestral, semestral, anual ou até mesmo diário, poderemos utilizar os comandos abaixo:
alter table t2 set INTERVAL(NUMTOYMINTERVAL(3,'month'));
alter table t2 set INTERVAL(NUMTOYMINTERVAL(6,'month'));
alter table t2 set INTERVAL(NUMTOYMINTERVAL(1,'year'));
alter table t2 set INTERVAL(NUMTODSINTERVAL(1,'day'));Se quisermos desabilitar o recurso de criação automática de partições para a tabela T2, poderemos utilizar o comando abaixo:
alter table t2 set INTERVAL();No mais, poderíamos alterar o intervalo para que o mesmo fosse anual como demonstrado no comando abaixo:
SQL> alter table t2 set INTERVAL(NUMTOYMINTERVAL(1,'year'));
Tabela alterada.
SQL> select table_name,
2 partitioning_type,
3 partition_count,
4 interval
5 from user_part_tables
6 where table_name='T2';
TABLE_NAME PARTITION PARTITION_COUNT INTERVAL
----------- --------- --------------- -------------------------
T2 RANGE 1048575 NUMTOYMINTERVAL(1,'YEAR')Existem algumas restrições para seu uso:
- Não pode ser utilizado em tabelas do tipo IOT (Index Organized Tables).
- A coluna chave da partição deverá ser obrigatoriamente do tipo DATE ou NUMBER.
- Não poderá ser criado nenhum índice de domínio (Domain Indexes).
- Não é suportado no nível de sub-partição.
Neste momento, alguém poderia estar se perguntando. É possível exportar essas tabelas particionadas (Interval Partitioning) para o Oracle 10g? Sim. A tabela será importada no Oracle 10g sem problemas, mas as mesmas serão criadas com particionamento do tipo RANGE somente.
Exportando a tabela T1 no Oracle 11g para ser importada no Oracle 10g
C:\>expdp scott/tiger directory=data_pump_dir
dumpfile=t1 tables=t1 version=10.2
Export: Release 11.2.0.1.0 - Production on Qua Jun 8 20:41:12 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SCOTT"."SYS_EXPORT_TABLE_01": scott/********
directory=data_pump_dir dumpfile=t1 tables=t1 version=10.2
Estimativa em andamento com o método BLOCKS...
Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o método de BLOCKS: 896 KB
Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exportou "SCOTT"."T1":"SYS_P30" 43.95 KB 5000 linhas
. . exportou "SCOTT"."T1":"SYS_P31" 45.42 KB 5000 linhas
. . exportou "SCOTT"."T1":"SYS_P32" 48.83 KB 5000 linhas
. . exportou "SCOTT"."T1":"SYS_P33" 35.67 KB 3501 linhas
. . exportou "SCOTT"."T1":"P1" 12.64 KB 999 linhas
. . exportou "SCOTT"."T1":"SYS_P25" 5.718 KB 100 linhas
. . exportou "SCOTT"."T1":"SYS_P26" 5.718 KB 100 linhas
. . exportou "SCOTT"."T1":"SYS_P27" 5.718 KB 100 linhas
. . exportou "SCOTT"."T1":"SYS_P28" 5.718 KB 100 linhas
. . exportou "SCOTT"."T1":"SYS_P29" 5.718 KB 100 linhas
Tabela-mestre "SCOTT"."SYS_EXPORT_TABLE_01" carregada/descarregada com sucesso
******************************************************************************Importando a tabela T2 no Oracle 10g
C:\>impdp scott/tiger dumpfile=t1 tables=t1
Import: Release 10.2.0.1.0 - Production on Quarta-Feira, 08 Junho, 2011 20:54:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
With the Partitioning, OLAP and Data Mining options
Tabela-mestre "SCOTT"."SYS_IMPORT_TABLE_01" carregada/descarregada com sucesso
Iniciando "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** dumpfile=t1 tables=t1
Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
. . importou "SCOTT"."T1":"SYS_P30" 43.95 KB 5000 linhas
. . importou "SCOTT"."T1":"SYS_P31" 45.42 KB 5000 linhas
. . importou "SCOTT"."T1":"SYS_P32" 48.83 KB 5000 linhas
. . importou "SCOTT"."T1":"SYS_P33" 35.67 KB 3501 linhas
. . importou "SCOTT"."T1":"P1" 12.64 KB 999 linhas
. . importou "SCOTT"."T1":"SYS_P25" 5.718 KB 100 linhas
. . importou "SCOTT"."T1":"SYS_P26" 5.718 KB 100 linhas
. . importou "SCOTT"."T1":"SYS_P27" 5.718 KB 100 linhas
. . importou "SCOTT"."T1":"SYS_P28" 5.718 KB 100 linhas
. . importou "SCOTT"."T1":"SYS_P29" 5.718 KB 100 linhas
Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
O job "SCOTT"."SYS_IMPORT_TABLE_01" foi concluído com sucesso em 20:54:58
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Qua Jun 8 21:13:48 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> select table_name,
2 partitioning_type,
3 partition_count,
4 def_tablespace_name
5 from user_part_tables
6 where table_name='T1';
TABLE_NAME PARTITITION PARTITION_COUNT DEF_TABLESPACE_NAME
----------- ------------ --------------- -------------------
T1 RANGE 10 USERS