O particionamento de tabelas (Oracle Partitioning) é a capacidade de dividir fisicamente tabelas de seu banco dados, sem a necessidade de reescrever o código fonte de sua aplicação, pois logicamente a tabela particionada é como uma tabela normal de seu banco de dados e é capaz de receber as mesmas instruções SQL (DDL, DML e DCL) que qualquer outra tabela, sendo que a separação dos dados acontece somente no âmbito físico, de acordo com o que for especificado no momento do particionamento pelo desenvolvedor.
O particionamento é de grande utilidade quando estamos trabalhando com tabelas que tem uma massa de dados muito grande, pois a partir do momento que a tabela está particionada, os dados estão divididos fisicamente e na hora da busca das informações, um comando SELECT, por exemplo, você sempre irá buscar as informações em uma porção fisicamente menor.
Para particionar uma tabela, podemos fazer isso em dois modos, o primeiro no momento de criação da tabela, ou então em um segundo momento, é quando a tabela já está criada e você quer particioná-la utilizando uma package do Oracle chamada DBMS_REDEFINITION. Hoje criaremos um tabela particionada nova.
Neste artigo vamos ter uma tabela que armazenará informações sobre pagamentos de mensalidades de alunos.
Vamos criar três tablespaces que armazenará os dados de nossas partições, considerando a separação de pagamentos de mensalidades acumuladas por ano:
SQL> create tablespace pagto_2015 datafile '/u01/oradata/orcl/tbs_pagto_2015_01.dbf' size 100m; Tablespace created. SQL> create tablespace pagto_2016 datafile '/u01/oradata/orcl/tbs_pagto_2016_01.dbf' size 100m; Tablespace created. SQL> create tablespace pagto_2017 datafile '/u01/oradata/orcl/tbs_pagto_2017_01.dbf' size 100m; Tablespace created.
Certo, agora crie a tabela de pagamentos de mensalidades de alunos particionada por ano, como demonstrado abaixo por range:
SQL> create table pagto_alunos
2 ( id_pagto number not null,
3 id_aluno number not null,
4 valor_mensalidade number(10,2),
5 data_boleto date,
6 data_vencimento date
7 ) partition by range (data_boleto)
8 ( partition p_pagto_2015 values less than (to_date('31/12/2015','dd/mm/rrrr')) tablespace pagto_2015,
9 partition p_pagto_2016 values less than (to_date('31/12/2016','dd/mm/rrrr')) tablespace pagto_2016,
10 partition p_pagto_2017 values less than (to_date('31/12/2017','dd/mm/rrrr')) tablespace pagto_2017
11 );
Table created.
SQL>
Ok, agora vamos popular nossas três partições com o bloco anônimo PLSQL abaixo:
SQL> declare
2 v_data date;
3 begin
4
5 v_data := to_date('01/01/2015','dd/mm/rrrr');
6
7 for x in 1 .. 1500000
8 loop
9 v_data := v_data + 1;
10
11 if v_data = to_date('31/12/2017','dd/mm/rrrr') then
12 v_data := to_date('01/01/2015','dd/mm/rrrr');
13 end if;
14
15 insert into pagto_alunos(id_pagto,
16 id_aluno,
17 valor_mensalidade,
18 data_boleto,
19 data_vencimento)
20 values (x,100,333.49,v_data,v_data+30);
21
22 end loop;
23 commit;
24 end;
25 /
PL/SQL procedure successfully completed.
SQL>
Realize uma contagem para você ter uma ideia da quantidade de registros que foram distribuídas entre as partições:
SQL> select count(*) from pagto_alunos;
COUNT(*)
----------
1500000
SQL> select count(*) from pagto_alunos where data_boleto < to_date('31/12/2015','dd/mm/rrrr');
COUNT(*)
----------
498679
SQL> select count(*) from pagto_alunos where data_boleto < to_date('31/12/2016','dd/mm/rrrr');
COUNT(*)
----------
1000099
SQL> select count(*) from pagto_alunos where data_boleto < to_date('31/12/2017','dd/mm/rrrr');
COUNT(*)
----------
1500000
SQL>
Certo, agora para nosso teste de comparativo de performance, crie um tabela não particionada cópia da nossa tabela de pagamentos. O nome desta tabela aqui colocamos: pagto_alunos_sem_particao.
SQL> create table pagto_alunos_sem_particao as select * from pagto_alunos; Table created. SQL>
Repare que uma é particionada e outra não:
SQL> col Table_Name format a30 col Partitioned format a10 select A.Table_Name, A.Partitioned from dba_tables a where table_name like 'PAGTO_ALUNOS%'; TABLE_NAME PARTITIONE ------------------------------ ---------- PAGTO_ALUNOS YES PAGTO_ALUNOS_SEM_PARTICAO NO SQL>
Agora compare o plano de execução das duas consultas. Uma realizada em nossa tabela particionada e outra em nossa tabela normal ou não particionada que tem a mesma quantidade e os mesmos registros.
Veja que em uma tabela não particionada, neste cenário aqui, é muito mais custoso para o banco retornar os dados, além de ter que fazer uma leitura na tabela toda ( full table scan ). Já na tabela particionada apenas uma partição(PARTITION RANGE SINGLE) será lida, veja:
Plano de Execução: Tabela Particionada:
SQL> explain plan for
2 select * from pagto_alunos where data_boleto = to_date('15/11/2016','dd/mm/rrrr');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1156874054
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------|
0 | SELECT STATEMENT | | 1247 | 71079 | 909 (10)| 00:00:01 | | ||
1 | PARTITION RANGE SINGLE| | 1247 | 71079 | 909 (10)| 00:00:01 | 2 | 2 ||
* 2 | TABLE ACCESS FULL | PAGTO_ALUNOS | 1247 | 71079 | 909 (10)| 00:00:01 | 2 | 2 |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DATA_BOLETO"=TO_DATE(' 2016-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
----------------
Plano de Execução: Tabela Não Particionada:
SQL> explain plan for
2 select * from pagto_alunos_sem_particao where data_boleto = to_date('15/11/2016','dd/mm/rrrr');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4150521251
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 677 | 38589 | 1939 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PAGTO_ALUNOS_SEM_PARTICAO | 677 | 38589 | 1939 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
1 - filter("DATA_BOLETO"=TO_DATE(' 2016-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
17 rows selected.
SQL>
Ok, agora vamos criar um índice local em nossa tabela particionada. Não vou entrar em detalhes do índice local, isso ficará para outro artigo. Apenas veremos mais um ganho através do plano de execução com a utilização em tabela particionadas:
SQL> CREATE INDEX pagto_alunos_idx ON pagto_alunos (data_boleto) LOCAL (PARTITION p_pagto_2015 TABLESPACE pagto_2015, PARTITION p_pagto_2016 TABLESPACE pagto_2016, PARTITION p_pagto_2017 TABLESPACE pagto_2017); Index created. SQL>
Agora compare este último plano de execução aos anteriores, repare que este foi o melhor se falando de plano de execução, veja que o índice está sendo utilizado:
SQL> explain plan for
2 select * from pagto_alunos where data_boleto = to_date('15/11/2016','dd/mm/rrrr');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3674619984
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1370 |78090 | 857 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1370 |78090 | 857 (0)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PAGTO_ALUNOS | 1370 |78090 | 857 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | PAGTO_ALUNOS_IDX | 1370 | | 8 (0)| 00:00:01 | 2 | 2 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DATA_BOLETO"=TO_DATE(' 2016-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
SQL>
Por hoje é só pessoal,
Ronaldo
Nenhum comentário:
Postar um comentário