Executando operações de manutenção em tabelas particionadas no Oracle
Olá,
Sabemos que o particionamento é uma técnica que permite que as tabelas e índices sejam divididos em componentes menores e mais gerenciáveis dentro de um banco de dados. As tabelas particionadas ajudam a tornar o banco de dados mais disponível e de melhor manutenção pelo fato de cada partição poder ser criada em tablespaces distintos. Por algumas razões de desempenho, cada partição de uma tabela pode e deve residir na sua própria tablespace. Uma outra vantagem é que as tabelas particionadas podem também melhorar o desempenho de consultas, pois quando uma operação de consulta é efetuada sobre uma tabela particionada, o otimizador determina a partição envolvida – característica denominada de Partition Pruning, desde que a operação esteja condicionada pela chave de partição.
Vale a pena salientar que o particionamento é transparente para as aplicações e não é necessária nenhuma alteração nas instruções SQL para tirar vantagem dele. No entanto, podem haver situações onde especificar uma partição diretamente na instrução SQL seria vantajoso. No mais, o objetivo deste artigo será apresentar de forma mais didática e, para quem já conhece um pouco sobre particionamento, algumas das operações básicas que podemos realizar nas partições de uma tabela particionada. Dentre algumas das operações temos: RENAME, MERGE, SPLIT, TRUNCATE, DROP, ADD, EXCHANGE, ANALYZE e MOVE. Para início, irei criar uma tabela de exemplo simples chamada T1. No caso, usarei o método range partition que foi um dos primeiros métodos de particionamento criado pela Oracle e existente desde a versão do Oracle 8.
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sex Fev 4 12:47:28 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> create table t1
2 (id number)
3 tablespace users
4 partition by range(id)
5 (
6 partition pdezenas values less than (100) tablespace tbs_dezenas,
7 partition pcentenas values less than (1000) tablespace tbs_centenas,
8 partition pmilhares_1000 values less than (2000) tablespace tbs_milhares,
9 partition pmilhares_2000 values less than (3000) tablespace tbs_milhares,
10 partition pmilhares_3000 values less than (4000) tablespace tbs_milhares,
11 partition pmilhares_4000 values less than (5000) tablespace tbs_milhares,
12 partition pmilhares_n values less than (maxvalue) tablespace tbs_milhares
13 );
Tabela criada.
SQL> create index idx_t1_id on t1 (id) local tablespace tbs_indx;
Índice criado.
SQL> insert into t1 select level from dual connect by level <= 10000;
10000 linhas criadas.
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');
Procedimento PL/SQL concluído com sucesso.Com os comandos acima, eu criei uma tabela particionada T1 com apenas uma coluna para fins de demonstração e a populei com 10 mil registros. Foi criado também um índice particionado localmente. Um índice particionado local é muito simples de configurar e manter porque o seu esquema de particionamento é idêntico ao esquema da tabela base. Em outras palavras, o número de partições do índice é igual ao número de partições da tabela. Para um fácil entendimento, eu criei 7 partições que armazenam números. A partição pdezenas foi criada na tablespace tbs_dezenas, a partição pcentenas foi criada na tablespace tbs_centenas e, as partições restantes, na tablespace tbs_milhares. Abaixo irei executar algumas instruções SQL para obter algumas informações pertinentes sobre a tabela particionada T1.
SQL> select table_name,
2 partitioning_type,
3 partition_count,
4 def_tablespace_name
5 from user_part_tables;
TABLE_NAME PARTITI PARTITION_COUNT DEF_TABLESPACE_NAME
-------------- ------- --------------- -------------------
T1 RANGE 7 USERSUtilizando a view de dicionário de dados USER_PART_TABLES, acima podemos ver que a tabela foi particionada pelo método RANGE e que a mesma possui atualmente 7 partições.
SQL> select index_name,
table_name,
partitioning_type,
locality,
alignment
from user_part_indexes;
INDEX_NAME TABLE_NAME PARTITI LOCALI ALIGNMENT
-------------- ---------------- -------- ------- ---------
IDX_T1_ID T1 RANGE LOCAL PREFIXEDUtilizando a view de dicionário de dados USER_PART_INDEXES, acima podemos ver que o índice IDX_T1_ID também foi particionado pelo método RANGE e que o mesmo é um índice LOCAL.
SQL> select * from USER_PART_KEY_COLUMNS;
NAME OBJEC COLUMN_NAME COLUMN_POSITION
----------- -------- ------------ ---------------
T1 TABLE ID 1
IDX_T1_ID INDEX ID 1Utilizando a view de dicionário de dados USER_PART_KEY_COLUMNS, acima podemos obter a coluna chave da partição. Abaixo a coluna PARTITIONED da view USER_TABLES nos mostra que a tabela T1 é uma tabela particionada.
SQL> select table_name,partitioned,num_rows from user_tables;
TABLE_NAME PAR NUM_ROWS
-------------------- --- ----------
T1 YES 10000Abaixo irei executar uma instrução SQL para obter da view USER_TAB_PARTITIONS algumas informações sobre os nomes das partições e o número de linhas em cada partição.
SQL> break on report
SQL> compute sum of num_rows on report
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 PDEZENAS 100 99
T1 PCENTENAS 1000 900
T1 PMILHARES_1000 2000 1000
T1 PMILHARES_2000 3000 1000
T1 PMILHARES_3000 4000 1000
T1 PMILHARES_4000 5000 1000
T1 PMILHARES_N MAXVALUE 5001
---------
sum 10000
7 linhas selecionadas.Vale a pena salientar que podemos também acessar diretamente os dados de uma partição como mostrado no exemplo abaixo:
SQL> select count(*) from t1 partition (pdezenas);
COUNT(*)
----------
99Uma curiosidade. Cada partição de tabela e índice é um segmento como mostrado na saída do SQL abaixo. Acessando a view USER_SEGMENTS, podemos ver os segmentos em suas tablespaces correspondentes.
SQL> select segment_name,
2 partition_name,
3 segment_type,
4 tablespace_name
5 from user_segments;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------- ----------------- ------------------ ---------------
T1 PDEZENAS TABLE PARTITION TBS_DEZENAS
T1 PCENTENAS TABLE PARTITION TBS_CENTENAS
T1 PMILHARES_1000 TABLE PARTITION TBS_MILHARES
T1 PMILHARES_2000 TABLE PARTITION TBS_MILHARES
T1 PMILHARES_3000 TABLE PARTITION TBS_MILHARES
T1 PMILHARES_4000 TABLE PARTITION TBS_MILHARES
T1 PMILHARES_N TABLE PARTITION TBS_MILHARES
IDX_T1_ID PDEZENAS INDEX PARTITION TBS_INDX
IDX_T1_ID PCENTENAS INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_1000 INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_2000 INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_3000 INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_4000 INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_N INDEX PARTITION TBS_INDX
14 linhas selecionadas.Podemos perceber acima que, por padrão, as partições de índices recebem o mesmo nome das partições de tabela. Bom, agora irei demonstrar algumas operações básicas que poderemos realizar nas partições em si.
----------------
RENAME PARTITION
----------------Renomear uma partição é bem simples. Para que fique visualmente mais elegante, irei renomear todas partiçõed de índice de forma que o nome fique diferente das partições de tabela.
SQL> alter index IDX_T1_ID rename partition PDEZENAS to PDEZENAS_INDX;
Índice alterado.
SQL> alter index IDX_T1_ID rename partition PCENTENAS to PCENTENAS_INDX;
Índice alterado.
SQL> alter index IDX_T1_ID rename partition PMILHARES_1000 to PMILHARES_1000_INDX;
Índice alterado.
SQL> alter index IDX_T1_ID rename partition PMILHARES_2000 to PMILHARES_2000_INDX;
Índice alterado.
SQL> alter index IDX_T1_ID rename partition PMILHARES_3000 to PMILHARES_3000_INDX;
Índice alterado.
SQL> alter index IDX_T1_ID rename partition PMILHARES_4000 to PMILHARES_4000_INDX;
Índice alterado.
SQL> alter index IDX_T1_ID rename partition PMILHARES_N to PMILHARES_N_INDX;
Índice alterado.
Após a execução dos comandos acima, podemos ver abaixo que as partições de índice foram renomeadas.
SQL> select segment_name,
2 partition_name,
3 segment_type,
4 tablespace_name
5 from user_segments
6 where segment_type = 'INDEX PARTITION';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------- -------------------- ------------------ ---------------
IDX_T1_ID PDEZENAS_INDX INDEX PARTITION TBS_INDX
IDX_T1_ID PCENTENAS_INDX INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_1000_INDX INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_2000_INDX INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_3000_INDX INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_4000_INDX INDEX PARTITION TBS_INDX
IDX_T1_ID PMILHARES_N_INDX INDEX PARTITION TBS_INDX
7 linhas selecionadas.
---------------
MERGE PARTITION
---------------Uma operação de MERGE significa mesclar ou combinar duas partições em uma só. No exemplo abaixo irei mesclar as partições PMILHARES_1000 e PMILHARES_2000 em uma nova partição que chamarei intencionalmente também de PMILHARES_2000. A operação de MERGE dropa as duas partições em questão e cria uma nova.
SQL> alter table t1 merge partitions
2 PMILHARES_1000,
3 PMILHARES_2000
4 into partition PMILHARES_2000 tablespace tbs_milhares;
Tabela alterada.
SQL> select segment_name,
2 partition_name,
3 segment_type,
4 tablespace_name
5 from user_segments
6 where segment_type='TABLE PARTITION';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------- ----------------- ------------------ ---------------
T1 PDEZENAS TABLE PARTITION TBS_DEZENAS
T1 PCENTENAS TABLE PARTITION TBS_CENTENAS
T1 PMILHARES_2000 TABLE PARTITION TBS_MILHARES
T1 PMILHARES_3000 TABLE PARTITION TBS_MILHARES
T1 PMILHARES_4000 TABLE PARTITION TBS_MILHARES
T1 PMILHARES_N TABLE PARTITION TBS_MILHARES
6 linhas selecionadas.No resultado do SQL acima, podemos ver que a partição PMILHARES_1000 não existe mais. Vale a pena salientar que será necessário reconstruir o índice da nova partição criada como demonstrado no exemplo abaixo:
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');
BEGIN dbms_stats.gather_table_stats('SCOTT','T1'); END;
*
ERRO na linha 1:
ORA-20000: index "SCOTT"."IDX_T1_ID" or partition of such index is in unusable state
ORA-06512: em "SYS.DBMS_STATS", line 13056
ORA-06512: em "SYS.DBMS_STATS", line 13076
ORA-06512: em line 1
SQL> select index_name,
2 partition_name,
3 high_value
4 from user_ind_partitions where status='UNUSABLE';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
----------- ------------------- ---------- --------
IDX_T1_ID PMILHARES_2000_INDX 3000 UNUSABLE
SQL> alter table t1 modify partition PMILHARES_2000 rebuild unusable local indexes;
Tabela alterada.
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');
Procedimento PL/SQL concluído com sucesso.
---------------
SPLIT PARTITION
---------------Dividir uma partição em duas também é bem simples. No exemplo abaixo, irei dividir a partição PMILHARES_N de forma a manter a partição PMILHARES_N e criar uma nova partição PMILHARES_5000.
SQL> select table_name,
2 partition_name,
3 num_rows
4 from user_tab_partitions where partition_name='PMILHARES_N';
TABLE_NAME PARTITION_NAME NUM_ROWS
-------------- ------------------ ----------
T1 PMILHARES_N 5001
SQL> alter table t1 split partition
2 PMILHARES_N at (6000)
3 into (partition PMILHARES_5000,
4 partition PMILHARES_N);
Tabela alterada.Assim como na operação de MERGE, será necessário recontruir os índices das partições envolvidas.
SQL> select index_name,
partition_name,
high_value
from user_ind_partitions where status='UNUSABLE';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
----------- ------------------- ---------- --------
IDX_T1_ID PMILHARES_5000 6000 UNUSABLE
IDX_T1_ID PMILHARES_N_INDX MAXVALUE UNUSABLE
SQL> alter table t1 modify partition PMILHARES_5000 rebuild unusable local indexes;
Tabela alterada.
SQL> alter table t1 modify partition PMILHARES_N rebuild unusable local indexes;
Tabela alterada.Bom, de acordo com o resultado do SQL abaixo, o esquema da tabela T1 após a operação de SPLIT ficou assim:
SQL> break on report
SQL> compute sum of num_rows on report
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 PDEZENAS 100 99
T1 PCENTENAS 1000 900
T1 PMILHARES_2000 3000 2000
T1 PMILHARES_3000 4000 1000
T1 PMILHARES_4000 5000 1000
T1 PMILHARES_5000 6000 1000
T1 PMILHARES_N MAXVALUE 4001
---------
sum 10000
------------------
TRUNCATE PARTITION
------------------Truncar uma partição não é diferente de truncar uma tabela não particionada.
SQL> alter table t1 truncate partition PMILHARES_N;
Tabela truncada.
SQL> break on report
SQL> compute sum of num_rows on report
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 PDEZENAS 100 99
T1 PCENTENAS 1000 900
T1 PMILHARES_2000 3000 2000
T1 PMILHARES_3000 4000 1000
T1 PMILHARES_4000 5000 1000
T1 PMILHARES_5000 6000 1000
T1 PMILHARES_N MAXVALUE 0
---------
sum 5999Podemos perceber que após a execução do comando TRUNCATE PARTITION na partição PMILHARES_N, a mesma teve os seus dados apagados.
--------------
DROP PARTITION
--------------Dropar uma partição também não é diferente de dropar uma tabela não particionada.
SQL> alter table t1 drop partition PMILHARES_N;
Tabela alterada.
SQL> break on report
SQL> compute sum of num_rows on report
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 PDEZENAS 100 99
T1 PCENTENAS 1000 900
T1 PMILHARES_2000 3000 2000
T1 PMILHARES_3000 4000 1000
T1 PMILHARES_4000 5000 1000
T1 PMILHARES_5000 6000 1000
---------
sum 5999Podemos perceber que após a execução do comando DROP PARTITION na partição PMILHARES_N, a mesma não existe mais.
-------------
ADD PARTITION
-------------Adicionar uma partição é bem simples. Veja o exemplo abaixo:
SQL> insert into t1 values (6500);
insert into t1 values (6500)
*
ERRO na linha 1:
ORA-14400: chave de partição inserida não está mapeada para partição algumaAo tentar inserir o registro 6500, o erro ORA-14400 foi emitido pelo fato da chave de partição inserida não está mapeada para nenhuma partição. Neste caso, irei criar uma nova partição chamada de PMILHARES_6000 que poderá armazenar valores até o número 7000.
SQL> alter table t1
2 add partition PMILHARES_6000
3 values less than (7000) tablespace tbs_milhares;
Tabela alterada.Após a criação da partição, poderemos inserir o registro sem maiores problemas como demonstrado no comando abaixo:
SQL> insert into t1 values (6500);
1 linha criada.
SQL> commit;
Commit concluído.
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');Bom, após a criação da nova partição, podemos verificar como ficou o esquema de partições da tabela T1.
SQL> break on report
SQL> compute sum of num_rows on report
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 PDEZENAS 100 99
T1 PCENTENAS 1000 900
T1 PMILHARES_2000 3000 2000
T1 PMILHARES_3000 4000 1000
T1 PMILHARES_4000 5000 1000
T1 PMILHARES_5000 6000 1000
T1 PMILHARES_6000 7000 1
---------
sum 6000
------------------
EXCHANGE PARTITION
------------------O comando EXCHANGE PARTITION é um método bem eficiente de realocar um segmento de tabela não particionada para uma tabela particionada. Poderemos perceber que o procedimento será muito mais rápido do que que realizar um INSERT na tabela. Vale a pena salientar que este recurso permite tanto mover o segmento de uma tabela não particionada para uma tabela particionada como o contrário. Para realizar esta demonstração irei criar uma tabela T2 com aproximadamente 10 milhões de registros. Primeiro irei recriar a partição PMILHARES_N com MAXVALUE para pode acomodar os registros que virão do segmento T2.
SQL> alter table t1 add partition PMILHARES_N
2 values less than (maxvalue)
3 tablespace tbs_milhares;
Tabela alterada.Agora irei criar a tabela T2 com aproximadamente 10 milhões de registros.
SQL> create table t2
2 tablespace tbs_milhares
3 as
4 select id from (select level id
5 from dual
6 connect by level <=10000000)
7 where id >= 7000;
Tabela criada.
SQL> select count(*) from t2;
COUNT(*)
----------
9993001Agora irei simular a inserção dos dados na tabela T1 provenientes da tabela T2 utilizando um INSERT SELECT.
SQL> set timing on
SQL> insert into t1 select * from t2;
9993001 linhas criadas.
Decorrido: 00:02:21.64Podemos perceber que a operação executou em 2 minutos e 21 segundos. Agora irei limpar os dados da partição e utilizar o método EXCHANGE PARTITION de forma a transferir os dados da tabela T2 para a tabela T1:
SQL> set timing off
SQL> alter table t1 truncate partition PMILHARES_N;
Tabela truncada.
SQL> set timing on
SQL> alter table t1
2 exchange partition PMILHARES_N
3 with table t2;
Tabela alterada.
Decorrido: 00:00:12.11Podemos perceber acima que a operação foi executada em aproximadamente 12 segundos, ou seja, nem se compara com o método INSERT SELECT.
SQL> set timing off
SQL> select count(*) from t2;
COUNT(*)
----------
0No mais, podemos perceber que após a operação, os dados na tabela T2 não existem mais, pois os mesmos foram movidos para a tabela T1. Abaixo, da mesma forma que outras operações, teremos também que reconstruir o índice da partição que sofreu a operação de EXCHANGE.
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');
BEGIN dbms_stats.gather_table_stats('SCOTT','T1'); END;
*
ERRO na linha 1:
ORA-20000: index "SCOTT"."IDX_T1_ID" or partition of such index is in unusable state
ORA-06512: em "SYS.DBMS_STATS", line 13056
ORA-06512: em "SYS.DBMS_STATS", line 13076
ORA-06512: em line 1
SQL> select index_name,
2 partition_name,
3 high_value
4 from user_ind_partitions where status='UNUSABLE';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
----------- ------------------- ---------- --------
IDX_T1_ID PMILHARES_N MAXVALUE UNUSABLE
SQL> alter table t1 modify partition PMILHARES_N rebuild unusable local indexes;
Tabela alterada.Após as operações realizadas acima, temos abaixo o esquema atual das partições da tabela T1.
SQL> break on report
SQL> compute sum of num_rows on report
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 PDEZENAS 100 99
T1 PCENTENAS 1000 900
T1 PMILHARES_2000 3000 2000
T1 PMILHARES_3000 4000 1000
T1 PMILHARES_4000 5000 1000
T1 PMILHARES_5000 6000 1000
T1 PMILHARES_6000 7000 1
T1 PMILHARES_N MAXVALUE 9993001
---------
sum 9999001
-----------------
ANALYZE PARTITION
-----------------Da mesma forma que podemos coletar estatísticas em uma tabela não particionada, poderemos também coletar estatísticas individualmente de partições como demonstrado abaixo:
SQL> analyze table t1 partition (PMILHARES_N) compute statistics;
Tabela analisada.
SQL> select table_name,
2 partition_name,
3 last_analyzed,
4 num_rows
5 from user_tab_partitions
6 where partition_name='PMILHARES_N';
TABLE_NAME PARTITION_NAME LAST_ANALY NUM_ROWS
------------- ------------------ ---------- ----------
T1 PMILHARES_N 04/02/2011 9993001
--------------
MOVE PARTITION
--------------Por fim, da mesma forma que podemos mover uma tabela não particionada para outra tablespace, poderemos também mover uma partição específica para outra tablespace. Na demonstração abaixo, a partição PDEZENAS se encontra na tablespace TBS_DEZENAS.
SQL> select table_name,
2 partition_name,
4 tablespace_name
4 where partition_name='PDEZENAS';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------- ------------------- ---------------
T1 PDEZENAS TBS_DEZENASIrei mover a partição PDEZENAS para a tablespace TBS_CENTENAS fazendo uso do comando MOVE PARTITION.
SQL> alter table t1 move partition PDEZENAS tablespace TBS_CENTENAS;
Tabela alterada.
SQL> select table_name,
2 partition_name,
4 tablespace_name
4 where partition_name='PDEZENAS';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------- ------------------- ---------------
T1 PDEZENAS TBS_CENTENASAssim como nas tabelas não particionadas, sempre após o comando MOVE, deveremos reconstruir o índice. Neste caso, irei reconstruir o índice da partição PDEZENAS.
SQL> alter table t1 modify partition PDEZENAS rebuild unusable local indexes;
Tabela alterada.