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.
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_NEWSFeito 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
