terça-feira, 8 de janeiro de 2013
Variáveis Booleanas
Muitos confundem a atribuição de 0 (zero) ou valores maiores que 0 (zero) como falso ou verdadeiro na atribuição a variáveis booleanas, aqui demonstro um simples script que esclarece esta situação.
declare
tb boolean; -- 0 = false, 1 = true
qtde integer ;
begin
qtde := 1222;
tb := ( qtde > 0 ) ;
if tb then
dbms_output.put_line ('true');
else
dbms_output.put_line ('false');
end if;
end;
Transformando tabelas particionadas em tabelas heap
Olá pessoal,
No artigo de hoje vou demonstrar como transformar uma tabela particionada em uma tabela heap sem ter que apagar os dados da tabela existente. Estou escrevendo este artigo, neste momento, porque acabei de fazer este procedimento em um Banco de Dados (BD) de Desenvolvimento na empresa em que eu trabalho.
Neste BD foi criada uma tabela particionada para que fossem efetuados testes de performance. Nela foi dada uma carga de 805.403.722 linhas. Para implementá-la em um BD de Produção precisaríamos obter licenciamento da option Oracle Partitioning. Por falta de “money” para comprar o licenciamento desta option, não vamos mais implementar a tabela particionada em produção e por isso tivemos que transformá-la em tabela heap.
Segue abaixo um passo-a-passo para efetuar a transformação:
Para iniciar o passo-a-passo abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus ou outra ferramenta compatível, com um usuário que tenha o privilégio SYSDBA.
Passo 1: Combinando partições:
Partindo do princípio que teremos uma tabela particionada chamada TAB_PART que contém 4 partições, iremos combinar as participações, de duas em duas, até restar uma só. Só é possível fazer a combinação (merge) de duas em duas, por isso, se você tiver muitas partições este processo pode ser um pouco demorado (comigo foi, pois a tabela que combinei tinha 30 partições). Ao efetuar as combinações, é necessário que as duas partições sejam combinadas na partição maior, ou seja, na partição que tem os números de blocos maiores. Se você fez o particionamento por range de data, por exemplo, é fácil identificar isso. É só combinar as duas partições na partição que tem as linhas com os maiores valores na coluna de data do particionamento!
Exemplo:
ALTER TABLE TAB_PART MERGE PARTITIONS P1, P2 INTO PARTITION P2;
ALTER TABLE TAB_PART MERGE PARTITIONS P2, P3 INTO PARTITION P3;
ALTER TABLE TAB_PART MERGE PARTITIONS P3, P4 INTO PARTITION P4;
ALTER TABLE TAB_PART MERGE PARTITIONS P2, P3 INTO PARTITION P3;
ALTER TABLE TAB_PART MERGE PARTITIONS P3, P4 INTO PARTITION P4;
Obs.: Ao término da execução dos comandos acima, sobrará somente a partição P4.
Passo 2: Criando uma nova tabela heap:
Criaremos agora uma nova tabela heap chamada TAB_HEAP, contendo a mesma estrutura (colunas, dados e constraints) da tabela particionada. Para criar essa tabela executaremos os passos a seguir:
a) Execute a procedure GET_DDL da package DBMS_METADATA para extrair o código DDL da tabela particionada, como no exemplo abaixo:
SELECT DBMS_METADATA.GET_DDL(‘TABLE’, ‘TAB_PART’, ‘OWNER’) FROM dual;
b) Pegue o script resultado do passo anterior e altere-o removendo as partições existentes e renomeando todas as constraints existentes, para um nome diferente do atual, de mode que não exista conflito de nomes entre constraints da tabela particionada e constraints da tabela heap. Aconselho guardar os nomes das constraints originais para usá-los novamente nos passos finais. O resultado final deste passo é um script para criar uma tabela heap.
c) Execute o script do passo anterior para criar a tabela heap.
Passo 3: Transferindo dados da tabela particionada para a tabela heap:
Agora iremos transferir os dados da tabela particionada para a nova tabela heap, executando o comando abaixo:
ALTER TABLE TAB_PART EXCHANGE PARTITION P4 WITH TABLE TAB_HEAP;
Obs.: Este passo tem um tempo de execução muito rápido. Após o seu término, verifique que a tabela particionada não possui mais dados. Todos eles foram transferidos para a tabela heap!
Passo 4: Apagando a tabela particionada:
Estamos chegando aos passos finais. A gente já tem uma tabela heap com os dados da tabela particionada, portanto, não precisamos mais da tabela particionada. Para apagá-la, execute o comando abaixo:
DROP TABLE TAB_PART PURGE;
Passo 5: Renomeando a tabela heap:
Agora iremos renomear a tabela heap para o nome original (nome anterior da tabela particionada):
ALTER TABLE TAB_HEAP RENAME TAB_PART;
Passo 6: Renomeando as constraints da tabela heap:
Para finalizar, iremos renomear as constraints da tabela heap para o nome original delas (nome anterior das constraints na tabela particionada), como no exemplo abaixo:
ALTER TABLE TAB_HEAP RENAME CONSTRAINT nome_novo TO nome_original;
Pronto! Terminamos de transferir todos os dados uma tabela particionada para uma tabela heap, sem apagar os dados da primeira! Existem outras formas de chegar ao mesmo resultado (Ex.: Export/Import ou CTAS), mas não sem apagar os dados da tabela de origem.
Por hoje é só!
[]s
Referências:
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;
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;
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 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.
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.
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.
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.
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.
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;
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);
(
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 :
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);
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
——————————————————————————-
——————————————————————————-
| 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);
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
—————————————————————————————————
| 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;
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;
Fonte: http://www.profissionaisti.com.br/2011/05/criando-tabelas-particionadas-para-otimizar-consultas/
Assinar:
Postagens
(
Atom
)
