Partition Table Oracle (Particionamento de Tabela)

Ontem me deparei com uma solicitação bastante interessante do cliente: Ele gostaria de particionar uma tabela, para melhorar a performance.  Mas... Que parada é essa de particionamento de tabela?

Em banco de dados, particionar significa segregar dados, ou seja, criar pedaços de objetos do banco de dados para melhorar o seu desempenho. Pode ser feito localmente (no mesmo banco de dados) ou distribuída geograficamente (num servidor em outro país, por exemplo).

Vale ressaltar que o particionamento é válido para big tables, ou seja, quando a tabela for povoada por uma grande carga de dados. E, ainda, cada partição deveria estar em discos diferentes, para melhorar a otimização e desempenho, porém, a maioria das pessoas acabam particionando no mesmo disco.

Existem 5 formas de particionamento:

1) Range (intervalo ou faixa), bem comum de ser encontrada, já que se baseia no campo data como chave do particionamento, organizando as partições por meses ou anos;

2) List (dividida por lista de valores);

3) Hash (administrado pelo próprio oracle) , que se baseia no valor de uma chave de particionamento, geralmente a primary key da tabela;

4) Range/hash (partição/subpartição). Aqui se cria uma partição range, e supartições em hash. O dba administra a faixa principal (tabela data com ano de 2015, por exemplo) e o oracle administra as subpartições (meses do ano de 2015, por exemplo);

5)  Range/List (Faixa/valores), em que dividem-se partições em faixa de valores e, após, em lista de valores.

Administrar partições é algo complexo, portanto na dúvida, aconselho a optar pelo modo hash, pois o oracle controla e administra todo o particionamento, sem que precisemos nos preocupar com isso. O “problema” é que não se sabe em que partição estará a informação.

Caso já seja um dba com mais rodagem, o recomendado é que se faça o particionamento por Range ou Range/Hash. Em bancos transacionais, vale a pena alocar 1 tablespace para hotblocks (áreas internas do disco que utilizam memória, e fazem o acesso de forma mais rápida), e alocar as outras tablespaces na área externa do disco, porque o acesso será mais lento do que pelos hotblocks.

Segue a baixo um passo a passo que criei para demonstrar como criar um particionamento em hash, como exportar e importar para outro schema uma tabela particionada. Para isso, tive que criar usuários, tablespaces, fazer export e import.

 

ATIVIDADE 1 : Criar tablespace TSTESTE

create tablespace TS_NEWS

datafile '/u02/oradata/orats01/ts_news.dbf'

size 40M

autoextend on next 40M maxsize 30G;

select TABLESPACE_NAME, FILE_NAME from dba_data_files;

ATIVIDADE 2 : Criar dois usuários, apontados para a TS_NEWS

create user salomao

identified by salomao

default tablespace TS_NEWS

temporary tablespace TEMP

password expire;

create user charles

identified by charles

default tablespace TS_NEWS

temporary tablespace TEMP

password expire;

select username,default_tablespace,temporary_tablespace

from dba_users

where username in ('CHARLES','SALOMAO');

  

ATIVIDADE 3 : Criar tabela e particioná-la (HASH)

ð  Caso necessite calcular o tamanho da tabela, para poder criar as tablespaces que serão usadas no particionamento, segue o comando sql:

select table_name,

decode(partitioned,'/','NO',partitioned) partitioned,

num_rows,

data_mb,

indx_mb,

lob_mb,

total_mb

from (select data.table_name,

partitioning_type

|| decode (subpartitioning_type,

 'none', null,

'/' || subpartitioning_type)

partitioned,

num_rows,

nvl(data_mb,0) data_mb,

nvl(indx_mb,0) indx_mb,

nvl(lob_mb,0) lob_mb,

nvl(data_mb,0) + nvl(indx_mb,0) + nvl(lob_mb,0) total_mb

from (  select table_name,

nvl(min(num_rows),0) num_rows,

round(sum(data_mb),2) data_mb

from (select table_name, num_rows, data_mb

from (select a.table_name,

a.num_rows,

b.bytes/1024/1024 as data_mb

from user_tables a, user_segments b

where a.table_name = b.segment_name))

group by table_name) data,

(  select a.table_name,

round(sum(b.bytes/1024/1024),2) as indx_mb

from user_indexes a, user_segments b

where a.index_name = b.segment_name

group by a.table_name) indx,

(  select a.table_name,

round(sum(b.bytes/1024/1024),2) as lob_mb

from user_lobs a, user_segments b

where a.segment_name = b.segment_name

group by a.table_name) lob,

user_part_tables part

where     data.table_name = indx.table_name(+)

and data.table_name = lob.table_name(+)

and data.table_name = part.table_name(+))

order by table_name;  

  Em seguida, criar as tablespaces que serão usadas nas partições:

create tablespace TS_PART1

datafile '/u02/oradata/orats01/ts_part1.dbf'

size 1M autoextend on next 1M maxsize 2M;

create tablespace TS_PART2

datafile '/u02/oradata/orats01/ts_part2.dbf'

size 1M autoextend on next 1M maxsize 2M;

select TABLESPACE_NAME, FILE_NAME from dba_data_files;

  

Logo após, dropei a tabela teste e a recriei particionando-a:

CREATE TABLE regions

   ( region_id NUMBER

   CONSTRAINT region_id_nn NOT NULL

   , region_name VARCHAR2(25)

   )

partition by hash (region_id)

partitions 2;

store in (TS_PART1,TS_PART2);

CREATE UNIQUE INDEX reg_id_pk

         ON regions (region_id);

ALTER TABLE regions

         ADD ( CONSTRAINT reg_id_pk

   PRIMARY KEY (region_id)

   ) ;

INSERT INTO regions VALUES

   ( 1

   , 'Europe'

   );

INSERT INTO regions VALUES

   ( 2

   , 'Americas'

   );

INSERT INTO regions VALUES

   ( 3

   , 'Asia'

   );

INSERT INTO regions VALUES

   ( 4

   , 'Middle East and Africa'

   );

 ATIVIDADE 4 : exportar full o banco e depois dropar as tabelas.

Criei o diretório export no /u01

Criei o diretório expdp, direcionando para /u01/export e dei os grants de leitura, escrita e exportação para o usuário salomao.

SQL> create directory expdp as '/u01/export';

SQL> grant read,write on directory expdp to salomao;

SQL> grant datapump_exp_full_database to salomao;

 

Executei o comando de datapump:

expdp directory=expdp dumpfile=exp_full_salomao_20160315.dmp logfile=exp_full_salomao_20160315.log full=y

ATIVIDADE 5 : Fazer o Import da tabela RE0GIONS para o usuário Charles

Antes de fazer o impdp é necessário da as permissões para o usuário de destino, no caso o charles.

grant read,write on directory expdp to charles;

grant unlimited tablespace to charles;

alter user charles quota unlimited on TS_NEWS

Feito isso, podemos partir para o import:

impdp directory=expdp dumpfile=exp_full_salomao_20160315.dmp logfile=imp_full_salomao_20160315.log tables=salomao.REGIONS partition_options=merge remap_schema=salomao:Charles table_exists_action=replace

** commando para gerar o import: impdp

** diretório onde se encontra o caminho do export: directory=expdp

** nome do arquivo de dump: dumpfile=exp_full_salomao_20160315.dmp

** novo nome do arquivo de log: logfile=imp_full_salomao_20160315.log

** nome da tabela que será importada: tables=salomao.REGIONS

** comando para mudar o owner: remap_schema=salomao:Charles

** comando para sobrescrever a tabela já existente: table_exists_action=replace

** comando para importar tabelas particionadas: partition_options=merge

Execução do script de recompilação, para validar o import:

SQL>@?/rdbms/admin/utlrp

 

E, por fim, conectando com o usuário Charles, basta fazer a consulta na tabela importada e ver que ela está lá.

My Linkedin: https://www.linkedin.com/in/salom%C3%A3o-netto-0667851a?trk=nav_responsive_tab_profile