Minhas Dicas: Criando tabelas particionadas para otimizar consultas

Pessoal,

Neste artigo vou apresentar para vocês o conceito de tabelas particionadas no Oracle Database e vou demonstrar como criar uma tabela particionada para possibilitar ganho de perfomance no acesso e atualização dos dados.

Uma tabela particionada é uma tabela dividida em partes menores, chamadas partições, para facilitar o gerenciamento e possibilitar melhor desempenho em consultas e atualizações.

As principais características das partições são:

- Possuem os mesmos atributos lógicos:
Todas as partições possuem as mesmas colunas, constraints e índices;

- Atributos físicos diferentes:
Para melhor desempenho as partições devem ser armazenadas em tablespaces distintos. Se possível, cada tablespace deve ser armazenado em um disco diferente;

- São transparentes para as aplicações:
As aplicações referenciam as tabelas particionadas do mesmo modo que referenciam as tabelas heap (normais), pois as aplicações fazem referência às tabelas e não às partições.

As tabelas podem ser particionadas através de vários métodos:

- Particionamento por faixa:
As partições são divididas em faixas lógicas de valores de colunas, como por exemplo, meses de um ano.

- Particionamento por hash:
As partições são divididas com base no valor hash de uma chave de particionamento.

- Particionamento por lista:
As partições são divididas por listas discretas de valores, fornecidas pelo DBA.

- Particionamento por faixa/hash:
As partições são divididas utilizando-se como base o método de faixa e, em cada partição por faixa, criando-se subpartições por hash.

- Particionamento por faixa/lista:
As partições são divididas primeiro em uma faixa de valores e, depois, com base em valores discretos.

Para criar tabelas particionadas, no Oracle Database 10G Enterprise Edition (versão do Oracle mais utilizada), é necessário obter licensiamento da option Oracle Partitioning (verhttp://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#CIHJHABF).

A melhor forma de otimizar o acesso e atualização dos dados em tabelas particionadas é armazenar as partições em discos diferentes. A minha recomendação principal para criar tabelas particionadas é utilizar este recurso somente quando uma tabela irá armazenar uma “enorme” quantidade de dados. Tabelas pequenas (com poucas linhas e/ou colunas com poucos dados) dificilmente terão ganhos de performance se forem particionadas.

O método de particionamento mais comumente utilizado é o método de Particionamento por faixa, método que utilizaremos como exemplo neste artigo. Para demonstrar o ganho de performance em uma consulta em tabelas particionadas, criaremos 2 tabelas com a mesma estrutura e mesmos dados. A tabela CLIENTE será uma tabela heap (normal) e a tabelaCLIENTE_PART será uma tabela particionada, com 3 partições, divididas por faixas de valores através da coluna que armazena a data de nascimento. As tabelas armazenarão cada uma 300 mil registros de clientes.

————————————————————————–

Para iniciar o passo-a-passo abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, SQL Delevoper ou outra ferramenta compatível, com um usuário que tenha privilégios para criar tabelas e tablespaces. De preferência não se conecte ou crie as tabelas no schema do usuário SYS.

————————————————————————–

PASSO 1: Criando os tablespaces que irão armazenar a tabela heap (CLIENTE) e as 3 partições da tabela particionada (CLIENTE_PART):

————————————————————————–

Se possível, crie os tablespaces TBS_PART_ATE_1920, TBS_PART_1920_1970 e TBS_PART_MAIOR_1970 em discos diferentes.

————————————————————————–

CREATE TABLESPACE TBS_NORMAL LOGGING DATAFILE ‘clientes_normal.dbf‘ SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_PART_ATE_1920 LOGGING DATAFILE ‘clientes_PART_ATE_1920.dbf‘ SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_PART_1920_1970 LOGGING DATAFILE ‘clientes_PART_1920_1970.dbf‘ SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_PART_MAIOR_1970 LOGGING DATAFILE ‘clientes_PART_MAIOR_1970.dbf‘ SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Obs.: Substitua os nomes dos arquivos (em vermelho) pelo caminho completo do arquivo (pasta + nome do arquivo).

Ex. no Windows:

C:\Oracle\10GR2\ORACLE_SID\clientes_normal.dbf —> ORACLE_SID = nome do BD Ex. no Linux:

/ora01/dados/ORACLE_SID/clientes_normal.dbf —> ORACLE_SID = nome do BD

PASSO 2: Criando as tabelas para armazenar clientes:

a) Criando a tabela heap:

CREATE TABLE CLIENTES (
ID_CLIENTE                          NUMBER(6,0) PRIMARY KEY ,
NM_CLIENTE                       VARCHAR2(50) NOT NULL ENABLE,
NR_ANO_NASCIMENTO    NUMBER(4) NOT NULL ENABLE,
ID_SEXO                                CHAR(1),
NR_CPF                                  VARCHAR2(11)
) TABLESPACE TBS_NORMAL;

b) Criando a tabela particionada:

CREATE TABLE CLIENTES_PART
(
ID_CLIENTE                          NUMBER(6,0) PRIMARY KEY ,
NM_CLIENTE                       VARCHAR2(50) NOT NULL ENABLE,
NR_ANO_NASCIMENTO   NUMBER(4) NOT NULL ENABLE,
ID_SEXO                               CHAR(1),
NR_CPF                                  VARCHAR2(11)
)
PARTITION BY RANGE (NR_ANO_NASCIMENTO)
(
PARTITION PART_ATE_1920 VALUES LESS THAN (1920) TABLESPACETBS_PART_ATE_1920,
PARTITION PART_1920_1970 VALUES LESS THAN (1970) TABLESPACETBS_PART_1920_1970,
PARTITION PART_MAIOR_1970 VALUES LESS THAN (MAXVALUE) TABLESPACETBS_PART_MAIOR_1970);

PASSO 3: Inserindo dados (300.000 linhas) nas 2 tabelas criadas no passo anterior :

DECLARE
I NUMBER;
v_ano number := 1800;
BEGIN
for i in 1 .. 100000 loop
INSERT INTO CLIENTES
(ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES
(I, ‘Nome ‘ || I, V_ANO, ‘M’);
INSERT INTO CLIENTES_PART
(ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES
(I, ‘Nome ‘ || I, V_ANO, ‘M’);
v_ano := v_ano + 1;
IF V_ANO = 1919 THEN
v_ano := 1800;
end if;
end loop;
V_ANO := 1920;
for i in 100001 .. 200000 loop
INSERT INTO CLIENTES
(ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES
(I, ‘Nome ‘ || I, v_ano, ‘F’);
INSERT INTO CLIENTES_PART
(ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES
(I, ‘Nome ‘ || I, v_ano, ‘F’);
v_ano := v_ano + 1;
IF V_ANO = 1969 THEN
V_ANO := 1920;
end if;
end loop;
V_ANO := 1970;
for i in 200001 .. 300000 loop
INSERT INTO CLIENTES
(ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES
(I, ‘Nome ‘ || I, v_ano, ‘F’);
INSERT INTO CLIENTES_PART
(ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES
(I, ‘Nome ‘ || I, v_ano, ‘F’);
v_ano := v_ano + 1;
IF V_ANO = 2011 THEN
V_ANO := 1970;
end if;
END LOOP;
COMMIT;
END;

Passo 4: Comparando o desempenho de consultas entre as tabelas normal e particionada:

a) Gerando o plano de execução de uma consulta na tabela heap (CLIENTES), filtrando os dados de retorno para exibir somente clientes que nasceram no ano de 1920:

EXPLAIN PLAN FOR
SELECT * FROM CLIENTES
WHERE NR_ANO_NASCIMENTO IN (1920);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Resultado:

——————————————————————————-

| Id | Operation                         | Name            | Rows | Bytes | Cost (%CPU)| Time
——————————————————————————-
|   0 | SELECT STATEMENT |                       | 1610 | 90160 |   284   (3)       | 00:00:04
|* 1 | TABLE ACCESS FULL| CLIENTES | 1610 | 90160 |   284   (3)        | 00:00:04
——————————————————————————-

Obs.: No meu ambiente de testes, o plano de execução informou que o tempo de retorno da consulta é de 4s.

b) Gerando o plano de execução de uma consulta na tabela particionada (CLIENTES_PART), filtrando os dados de retorno para exibir somente clientes que nasceram no ano de 1920:

EXPLAIN PLAN FOR
SELECT * FROM CLIENTES_PART
WHERE NR_ANO_NASCIMENTO IN (1920);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Resultado:

—————————————————————————————————
| Id | Operation                                      | Name          | Rows | Bytes | Cost (%CPU)| Time
—————————————————————————————————
|   0 | SELECT STATEMENT               |                     | 2157 |   117K|   123 (11)       | 00:00:02
|   1 | PARTITION RANGE SINGLE|                            | 2157 |   117K|   123 (11)| 00:00:02
|* 2 |   TABLE ACCESS FULL    | CLIENTES_PART | 2157 |   117K|   123 (11)| 00:00:02
—————————————————————————————————

Obs.: No meu ambiente de testes, o plano de execução informou que o tempo de retorno da consulta é de 2s.

————————————————————————–

Para mais informações sobre Full Table Scan (FTS), índices e planos de execução, consulte o artigo Guia de tuning para instruções SQL postado em 22/09/2010

————————————————————————–

CONCLUSÃO

Nos testes deste artigo pudemos verificar uma situação em que tivemos um ganho de performance de 50% no tempo de execução de uma consulta ao utilizar uma tabela particionada. Em determinadas situações, principalmente em tabelas muito grandes, o ganho de performance pode ser ainda maior, mas ressalto que, nem todas as consultas ou atualizações terão o mesmo desempenho. Se a tabela for pequena o tempo de execução pode até piorar.

No exemplo que vimos neste artigo, a consulta foi efetuada filtrando dados em uma coluna que não tinha índices, portanto, o Oracle teve que fazer um FULL TABLE SCAN (FTS) na tabela. O tempo do FTS na tabela particionada foi menor do que na tabela heap, pois os dados do retorno da consulta estavam todos na partição do tablespace TBS_PART_1920_1970, que tinha menos dados que o tablespace TBS_NORMAL, da tabela heap.

Script para limpeza dos testes efetuados neste artigo:

DROP TABLE CLIENTES PURGE;
DROP TABLE CLIENTES_PART PURGE;
DROP TABLESPACE TBS_NORMAL INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_ATE_1920 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_1920_1970 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_MAIOR_1970 INCLUDING CONTENTS AND DATAFILES;