Interval Partitioning. Abordando um conceito de particionamento por intervalo do Oracle Database.

Por Eduardo Legatti
Postado em Abril de 2014

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             100

Pois 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               5000

Perceberam 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 dotipo 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 - Product 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


Eduardo Legatti é Analista de Sistemas e Administrador de banco de dados. É pós graduado em Gerência da Tecnologia da Informação, possui as certificações OCA 9i - OCP 9i/10g/11g – OCE SQL Expert, e vem trabalhando como DBA Oracle desde a versão 8.0.5. Se interessa particularmente em planejar estratégias de backup/recovery, performance tuning e projetos de bancos de dados (modelagem física e lógica) atuando como consultor. Como Oracle ACE, ele tem o hábito de disseminar seu conhecimento através de artigos, grupos de discussão (Oracle OTN Forums) e dedica-se a compartilhar informações de forma a motivar novos DBAs. Frequentemente posta artigos em seu Oracle blog http://eduardolegatti.blogspot.com.