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


quarta-feira, 8 de junho de 2011

Abordando o particionamento por intervalo (INTERVAL PARTITIONING) disponível a partir do Oracle 11g

Por Eduardo Legatti

Olá,

No artigo de Fevereiro/2011 fiz uma breve introdução sobre o conceito de tabelas particionadas, assim como no post de Abril/2011, na qual demonstrei como particionar uma tabela de forma on-line. 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 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



5 comentários:

Danilo Carvalho disse...

Oi Eduardo,

Ótimo post! Chega de jobs para criar partições, rs

Abraço

Priscila disse...

Olá Legatti, ótimo post, parabéns!
Uma duvida, no Oracle 10g e 11g, há uma opção de particionar a tabela por mês independente do ano? ou seja, criar uma partição para o mês 10, e os dados do mes 10/2014 e 10/2015 e assim por diante, caia nesta partição?
Obrigada!

Eduardo Legatti disse...

Olá Priscila,

Realmente nunca tinha visto esse caso ;-) Particionar apenas pelo mês ignorando o ano? Não sei se faria muito sentido. Bom, imaginando que você queira agrupar todos os registros de um mês independente do ano em uma única partição, então não seria com o tipo DATE que se faria isso. A tabela deverá ter uma coluna "MES" do tipo NUMBER. Ao inserir um registro na tabela, o mês da data seria extraído e inserido na coluna "MES". Neste caso a tabela seria particionada por esse campo "MES". Outra coisa é que essa coluna poderá ser virtual ;-) Dá uma pesquisada, pois no Oracle 11g é possível criar colunas virtuais e inclusive particionar a tabela por elas ;-)

Abraços

Legatti

Priscila disse...

Legatti, muito obrigada!
Tenho lido os seus artigos e estou aprendendo muito, você está de parabéns!
Legatti, sobre o particionamento automático, existe alguma desvantagem? Pois o Oracle criará as partições conforme a necessidade, porém no caso de inserir milhões de registros, e no momento da execução precise criar varias partições, isso levará mais tempo, pois o Oracle terá que criar as partições, levará mais tempo do que se a partição já existisse, existe alguma restrição sobre o particionamento automático?
Agradeço desde de já.

Eduardo Legatti disse...

Olá Priscila,

A única desvantagem é que os nomes das partições são geradas pelo Oracle (SYS_P). Se isso for um problema, nada impede que as partições sejam renomeadas. Em relação ao impacto de criação das partições, não vejo que isso possa acarretar em problemas significativos de performance. O que você pode fazer é tentar simular (medir e comparar as duas abordagens). Quanto às restrições, é o que eu comentei no artigo:

* 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

Uma novidade legal no Oracle 12c é que agora é possível utilizar o Reference Partitioning em tabelas que são particionadas com o Interval Partitioning. ;-)

Abraços e até mais ...

Legatti

Postagens populares