Conhecendo a Técnica de Fragmentação no SGBD Oracle

Neste artigo é realizado um exemplo de Fragmentação de dados, técnica muito utilizada em Sistemas de Banco de Dados Distribuídos. Para isso a linguagem SQL será utilizada no Oracle 11g Express Edition e será criada e configurada uma rede wireless no Windows 7 para a comunicação entre as máquinas e compartilhamento de informações dos seus bancos de dados.

Fragmentação

Segundo Oszu e Valduriez (2001), em Sistemas de Banco de Dados Distribuídos, por razões de desempenho, confiabilidade e disponibilidade, é desejável que os dados sejam distribuídos pelas máquinas de uma rede de forma replicada. Uma das técnicas muito utilizadas para esse fim é a fragmentação, onde as relações de um banco de dados são divididas em fragmentos menores e cada fragmento é tratado como um objeto de banco de dados separado. Nessa técnica, cada réplica não é a replicação completa, mas apenas um subconjunto dessa relação, assim é exigido menos espaço e consequentemente menos itens de dados precisam ser administrados.

A Fragmentação pode ser horizontal, vertical ou híbrida. Na primeira, uma relação é particionada em suas tuplas (linhas) e cada fragmento gerado tem um subconjunto das tuplas da relação original. Na fragmentação vertical são produzidos fragmentos que contêm um subconjunto dos atributos (colunas) da relação original, bem como sua primary key. Também chamada de fragmentação mista ou aninhada, a fragmentação híbrida consiste na aplicação das duas técnicas citadas anteriormente, uma após a outra. Ela é utilizada porque, na maioria dos casos, uma fragmentação vertical ou horizontal não será suficiente para atender aos requisitos de aplicativos do usuário.

Existem duas versões da Fragmentação Horizontal: primária e derivada. A Fragmentação Horizontal Primária é executada com o uso de predicados definidos sobre a própria relação, já a Fragmentação Horizontal Derivada surge do particionamento de uma relação, que é resultado da definição de predicados sobre outra relação. Neste artigo será utilizada a técnica de Fragmentação Horizontal Primária.

O principal objetivo da fragmentação é minimizar o tempo de processamento dos aplicativos do usuário, logo, mesmo sendo baseadas em uma relação completa, as consultas são executadas sobre os fragmentos.

Estudo de caso – Cadastro de Funcionários

Criação e configuração da rede.

Em Sistemas de Bancos de Dados Distribuídos, a rede é um dos recursos mais importantes, logo, para que se configure a distribuição as várias máquinas que compartilham informações, as mesmas precisam estar interconectadas através de uma rede, de maneira transparente.

Para realizar a comunicação entre a máquina 1 e a máquina 2 do estudo de caso Cadastro de Funcionários será criada e configurada uma rede ad hoc, que é uma rede sem fio que dispensam o uso de um ponto de acesso comum aos computadores conectados a ela, de modo que todos os dispositivos da rede funcionam como se fossem um roteador, encaminhando comunitariamente informações que vêm de dispositivos vizinhos.

Para a criação da rede, execute o prompt de comando do Windows como administrator e digite o comando a seguir:

netsh wlan set hostednetwork ssid=remoteServer key=serverkey12

O resultado da execução pode ser visto na Figura 1.

Figura 1. Comando para criação da rede ad hoc.

Nesse caso, como já havia uma rede hospedada, o comando apenas fez a alteração das informações da rede. Após a criação da rede, para que esta fique disponível de modo que os usuários se conectem é necessário que a rede seja iniciada. Para isso, digite o comando para iniciação de redes ad hoc no prompt de comando do Windows:

netsh wlan start hostednetwork

Criada e ativada a rede, a maquina 2 poderá conectar-se a ela usando o nome de usuário e senha configurados para a rede. Depois de conectado é importante testar a conectividade utilizando o comando de rede Ping, que deve ser executado no prompt de comando do Windows. A conectividade estará estabelecida com sucesso caso não haja perda de pacotes no resultado do comando, conforme a Figura 2.

Figura 2. Comando para testar a conectividade entre as máquinas interligadas.

Caso a rede não fique disponível para os usuários da máquina na qual esta foi configurada, verifique o firewall do Windows, que pode impedir a disponibilidade da rede por questões de segurança. Assim, será necessário configurá-lo para permitir conexões de entrada. Para isso, percorra o caminho Painel de Controle/Sistema e Segurança/Firewall do Windows/Configurações avançadas para habilitar conexões de entrada no Windows.

Ao clicar em configurações avançadas, a tela da Figura 3 será exibida. Clique em Propriedades do firewall do Windows.

Figura 3.Tela de configurações avançadas do firewall do Windows.

Será aberta uma tela, como a exibida na Figura 4. Em conexões de entrada, escolha a opção permitir para todos os perfis e clique em Ok.

Figura 4. Tela de configuração das permissões de conexões de entrada.

Configuração do serviço para a criação do link

Para a criação de um link é necessário que se especifique o nome da conexão que contém os detalhes da máquina que se deseja conectar. Essas conexões são configuradas no arquivo tnsnames, que se encontra na pasta de instalação do Oracle, em C:/oraclexe/app/oracle/product/11.2.0/server/network/ADMIN.

Na Figura 5 vemos o arquivo tnsnames e as linhas em evidência devem ser adicionadas ao arquivo, conforme a Listagem 1, para a configuração do serviço que será utilizado para acessar a máquina 2 do estudo de caso.

Listagem 1. Trecho a adicionar no arquivo tnsnames

  REMOTE = 
    (DESCRIPTION = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.251)(PORT = 1521))
         (CONNECT_DATA = 
             (SERVICE_NAME = XE)
         )
    )
  )

Figura 5. Arquivo tnsnames do Oracle.

Observe que um nome é dado à conexão criada. O protocolo do endereço IP, bem como o próprio IP da máquina que se deseja conectar devem ser definidos no arquivo. A porta de comunicação e o nome do serviço do SGBD que será utilizado também devem ser especificados. Nesse caso como a fragmentação será em SGBSs homogêneos (iguais), a porta utilizada será 1521 (pertencente ao Oracle) e o nome do serviço será XE, o padrão do Oracle.

Sintaxes para a criação e autenticação do link

Para que os dois bancos se comuniquem, é trivial a definição de um link. Com o link definido é possível que um banco de dados acesse objetos em outro banco de dados. A sintaxe para a criação de um link pode ser observada na Figura 6.

Na criação de um link, é obrigatória a especificação de um usuário e senha, tais parâmetros deverão receber o usuário e senha do banco de dados Oracle da máquina que se deseja acessar. O padrão de usuário de bancos de dados Oracle é system ou sys, e é necessária ainda a especificação do serviço configurado no arquivo de serviços do Oracle. Veremos o passo a passo da configuração mais à frente. A sintaxe para a autenticação do link pode ser observada na Figura 7.

Figura 6. Esquema da sintaxe para a criação de um link - ()dblink_authentication

Figura 7. Esquema da sintaxe para a autenticação de um link.

Na Listagem 2 encontra-se o código SQL para a criação do link que identificará o banco de dados que será acessado na máquina 2.

Listagem 2. Código SQL para a criação do link do banco de dados que será acessado pela máquina 1

  CREATE DATABASE LINK REMOTE_DB_MAQUINA2 
  CONNECT TO SYSTEM 
  IDENTIFIED BY “keymaquina2”
  USING REMOTE

Depois de criado, o link pode ser usado para referenciar objetos, como se fosse uma instância do banco que está sendo manipulada, como pode ser observado a seguir:

FUNCIONARIO_TB2@REMOTE_DB_MAQUINA2;

Criação das Tabelas FUNCIONARIO_TB1 e FUNCIONARIO_TB2

A Listagem 3 mostra o comando SQL utilizado para a criação da tabela FUNCIONARIO_TB1, que será criada no banco da máquina 1.

Listagem 3. Criação da tabela funcionario_tb1.

  CREATE TABLE FUNCIONARIO_TB1(
  ID_FUNCIONARIO INT PRIMARY KEY,
  NOME_FUNCIONARIO VARCHAR(80) NOT NULL,
  CPF_FUNCIONARIO VARCHAR(14) NOT NULL,
  CATEGORIA_FUNCIONARIO VARCHAR(20) NOT NULL,
  SALARIO_FUNCIONARIO NUMERIC(8,2) NOT NULL
  );

Na Figura 8 pode-se observar a saída para o comando.

Figura 8. Saída para o comando de criação da tabela funcionario_tb1.

A Figura 9 mostra a tabela criada.

Figura 9. Tabela funcionario_tb1 criada.

A tabela também deverá ser criada no banco de dados da maquina 2, como na Listagem 4. As Figuras 10 e 11 apresentam a saída para o comando e a tabela criada, respectivamente.

Listagem 4. Sintaxe para a criação da tabela funcionário do banco de dados da maquina 2.

  CREATE TABLE FUNCIONARIO_TB2(
  ID_FUNC INT PRIMARY KEY,
  NOME_FUNC VARCHAR(80) NOT NULL,
  CPF_FUNC VARCHAR(14) NOT NULL,
  CATEGORIA_FUNC VARCHAR(20) NOT NULL,
  SALARIO_FUNC NUMERIC(8,2) NOT NULL
  );

Figura 10. Saída para o comando de criação da tabela funcionario_tb2.

Figura 11. Tabela funcionario_tb2 criada.

De modo que não haja conflito de chaves na inserção de funcionários, é interessante que a chave seja automática. Para isso, é necessária a criação de uma sequence (Listagem 5) para o atributo identificador da tabela FUNCIONARIO_TB1 e um trigger (Listagem 6) que será acionado sempre que uma operação de inserção for realizada na tabela FUNCIONARIO_TB1.

Listagem 5. Criação da sequence para gerar chave automática para tabela funcionario_tb1.

  CREATE SEQUENCE AUTO_INCREMENT_FUNCIONARIO
  INCREMENT BY 1
  START WITH 1;

A sequence AUTO_INCREMENT_FUNCIONARIO define que a chave será incrementada de um em um e que esta será iniciará a partir do 1.

Listagem 6. Criação do gatilho para chamar a sequence auto_increment_funcionario.

  CREATE OR REPLACE TRIGGER INCREMENT_FUNCIONARIO_TRG
  BEFORE INSERT ON FUNCIONARIO_TB1 
  FOR EACH ROW 
  BEGIN
  SELECT AUTO_INCREMENT_USUARIO.NEXTVAL INTO:NEW.ID_FUNCIONARIO FROM DUAL;
  END;

O gatilho INCREMENT_FUNCIONARIO_TRG será disparado sempre depois de uma inserção na tabela funcionario_tb1. Nele será chamada a sequence AUTO_INCREMENT_FUNCIONARIO, assim, a chave primária da tabela sempre será incrementada automaticamente após uma inserção.

Predicado de fragmentação da tabela funcionario_tb1.

Um predicado de fragmentação deverá ser criado de modo a se definir uma condição para o particionamento da relação original. O atributo da tabela funcionario_tb1 que será usado para definição dos predicados será CATEGORIA_FUNCIONARIO, definindo a condição para que o funcionário seja manipulado no banco de dados da máquina 2:

P: (CATEGORIA_FUNCIONARIO = “terceirizado”) ^ (SALARIO_FUNCIONARIO ≤ 3000)

Conforme o predicado, no banco de dados da máquina 2 apenas serão inseridos funcionários terceirizados e que recebem salário igual ou inferior a R$ 3.000,00.

Operação de Inserção

Para que os dados que serão inseridos na tabela FUNCIONARIO_TB1 (máquina 1) sejam fragmentados e inseridos na tabela FUNCIONARIO_TB2 (máquina 2), será necessária a criação de um trigger (gatilho), que sempre deverá ser acionado depois de solicitada uma inserção na tabela funcionario_tb1. Observe na Listagem 7 a sintaxe.

Listagem 7. Trigger para a operação inserir.

  CREATE OR REPLACE TRIGGER INSERIRFUNCIONARIO
  BEFORE INSERT ON FUNCIONARIO_TB1
  FOR EACH ROW 
  BEGIN 
  IF (:NEW.CATEGORIA_FUNCIONARIO = ‘TERCEIRIZADO’ AND :NEW.SALARIO_FUNCIONARIO <= 3000) THEN
  INSERT INTO FUNCIONARIO_TB2@REMOTE_DB_MAQUINA2 VALUES (DEFAULT, :NEW.NOME_FUNCIONARIO, :NEW.CPF_FUNCIONARIO, :NEW.CATEGORIA_FUNCIONARIO, :NEW.SALARIO_FUNCIONARIO);
  END IF;
  END;

Para que o Oracle compare linguisticamente, ignorando caracteres maiúsculos e minúsculos, é necessário alterar a sessão corrente de modo a definir que esta não seja case sensitive, como na Listagem 8.

Listagem 8. Comando para alterar a sessão.

  ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_CI';
  ALTER SESSION SET NLS_COMP='ANSI';

O gatilho INSERIRFUNCIONARIO sempre será acionado depois que for realizada uma operação de inserção na tabela FUNCIONARIO_TB1. Caso o funcionário que está sendo inserido seja terceirizado e o valor do seu salário seja menor ou igual a R$ 3.000,00 também será inserido na tabela funcionario_tb2. Funcionários com atributos fora do requisito definido no predicado serão inseridos somente na tabela FUNCIONARIO_TB1. Na Figura 12 observe um exemplo de inserção nessa tabela.

Figura 12. Exemplo de inserção na tabela funcionario_tb1.

Na Figura 13 pode ser observada a tabela FUNCIONARIO_TB1 com os dados inseridos. Como neste exemplo foi inserido um funcionário com atributos que atendem aos requisitos definidos no predicado, os dados também foram inseridos na tabela funcionario_tb2. Isso pode ser observado através de um select (Listagem 10) sobre a view (Listagem 9) que seleciona os dados da tabela remota FUNCIONARIO_TB2, como na Figura 14.

Figura 13. Dados inseridos na tabela funcionario_tb1.

Listagem 9. View para selecionar os dados da tabela funcionario_tb2.

  CREATE VIEW FUNCIONARIO_TB_REMOTE AS
  SELECT * FROM FUNCIONARIO_TB2@REMOTE_DB_MAQUINA2;

Listagem 10. Select sobre a view funcionario_tb_remote.

SELECT * FROM FUNCIONARIO_TB_REMOTE;

Figura 14. Dados inseridos na tabela funcionario_tb2.

A Figura 15 mostra um novo exemplo de inserção onde os atributos categoria e salário não atendem aos requisitos do predicado.

Figura 15. Novo exemplo de inserção na tabela funcionario_tb1.

A Figura 16 apresenta os dados inseridos na tabela FUNCIONARIO_TB1 e a Figura 17 mostra que a tabela FUNCIONARIO_TB2 não sofreu nenhuma nova inserção.

Figura 16. Novos dados inseridos na tabela funcionario_tb1.

Figura 17. Tabela funcionario_tb2 sem novas inserções.

Operação de atualização

As atualizações na tabela FUNCIONARIO_TB1 serão realizadas conforme o predicado definido para realização da operação de inserção, de modo que os dados de funcionários inseridos na tabela FUNCIONARIO_TB2 também sejam corretos e mantenham a regra de permanência no banco de dados. Um novo trigger deverá ser criado para realizar as operações de atualização, como mostra a Listagem 11.

Listagem 11. Trigger para atualização da tabela funcionario_tb1

  CREATE OR REPLACE TRIGGER ATUALIZARFUNCIONARIO
  BEFORE UPDATE ON FUNCIONARIO_TB1
  FOR EACH ROW 
  BEGIN 
  IF(:NEW.CATEGORIA_FUNCIONARIO = ‘TERCEIRIZADO’ AND :NEW.SALARIO_FUNCIONARIO <= 3000) THEN
  UPDATE FUNCIONARIO_TB2@REMOTE_DB_MAQUINA2 SET NOME_FUNC = :NEW.NOME_FUNCIONARIO, CPF_FUNC = :NEW.CPF_FUNCIONARIO, CATEGORIA_FUNC = :NEW.CATEGORIA_FUNCIONARIO, SALARIO_FUNC = :NEW.SALARIO_FUNCIONARIO WHERE :OLD.ID_FUNCIONARIO = :OLD.ID_FUNCIONARIO;
  END IF;
  END;

O gatilho ATUALIZARFUNCIONARIO sempre será acionado depois de uma atualização na tabela FUNCIONARIO_TB1. Para que haja atualização na tabela funcionario_tb2 o predicado definido deve ser atendido, caso isso não ocorra, a atualização será feita apenas na tabela FUNCIONARIO_TB1. Observe um exemplo de atualização na Figura 18.

Figura 18. Exemplo de atualização na tabela funcionario_tb1.

A Figura 19 mostra a tabela FUNCIONARIO_TB1 e a Figura 20 a tabela FUNCIONARIO_TB2 com os dados atualizados.

Figura 19. Dados atualizados na tabela funcionario_tb1.

Figura 20. Dados atualizados na tabela funcionario_tb2.

Agora, observe um exemplo de atualização com dados que não atendem ao predicado na Figura 21. A Figura 22 apresenta a tabela atualizada.

Figura 21. Novo exemplo de atualização na tabela funcionario_tb1.

Figura 22. Nova atualização na tabela funcionario_tb1

Obviamente, como o valor do salário não atende ao pré-requisito definido, a tabela FUNCIONARIO_TB2 não sofrerá nenhuma modificação.

Operação de exclusão

A operação de exclusão na tabela FUNCIONARIO_TB1 deverá seguir a mesma lógica das operações anteriores, de modo que uma operação de delete nessa tabela exclua não só os dados da mesma, como também os dados na tabela FUNCIONARIO_TB2 correspondentes. Para isso criaremos um trigger, conforme a Listagem 12.

Listagem 12. Trigger para exclusão de dados na tabela funcionario_tb1.

  CREATE OR REPLACE TRIGGER DELETARFUNCIONARIO
  BEFORE DELETE ON FUNCIONARIO_TB1
  FOR EACH ROW 
  BEGIN 
  DELETE FROM FUNCIONARIO_TB2@REMOTE_DB_MAQUINA2 WHERE :OLD.ID_FUNCIONARIO = :OLD.ID_FUNCIONARIO;
  END;

O gatilho DELETARFUNCIONARIO sempre será disparado depois de uma operação de exclusão na tabela FUNCIONARIO_TB1, assim os dados dos funcionários fragmentados na tabela FUNCIONARIO_TB2 também serão excluídos. Observe na Figura 23 um exemplo de exclusão na tabela FUNCIONARIO_TB1.

Figura 23. Exemplo de exclusão na tabela funcionario_tb1.

As Figuras 24 e 25 mostram a tabela FUNCIONARIO_TB1 e FUNCIONARIO_TB2 depois da realização da operação de exclusão. Como o código de cada linha inserida na tabela FUNCIONARIO_TB1 foi replicado na tabela FUNCIONARIO_TB2, toda exclusão realizada na primeira também será realizada na segunda, portanto tal tabela encontra-se vazia.

Figura 24. Tabela funcionario_tb1 após operação de delete.

Figura 25. Tabela funcionario_tb2 vazia após operação de delete.

A técnica de fragmentação mostra-se eficiente por permitir que apenas parte de uma relação seja replicada, reduzindo assim o tempo e espaço com a administração de relações completas. Através da técnica também é possível dar respostas mais rápidas às solicitações de usuários, isso pode ser feito com o particionamento definido sobre a localidade do usuário, onde os dados estarão disponíveis em locais próximos a ele. Outra vantagem é que a disputa por recursos de processamento e de entrada e saída será reduzida. Vale lembrar que não é o administrador do banco de dados quem decide quando deve ou não fragmentar, mas os aplicativos do usuário, logo a fragmentação deverá ser realizada conforme sua necessidade.

Referências

Kotviski, Adriel; O QUE SÃO REDES AD HOC?
http://www.tecmundo.com.br/internet/2792-o-que-sao-redes-ad-hoc-.htm.

Tamer Ozsu, Patrick Valduriez; PRINCÍPIOS DE SISTEMAS DE BANCOS DE DADOS DISTRIBUÍDOS. Tradução Principies of Distributed Database Systems [2 ed. americana] Vandenberg D. de Sousa – Rio de Janeiro: Campus, 2001. ISBN 85-352-0713-9.

Oracle Database Online Documentation; CREATE DATABASE LINK
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm

Tanenbaum, Andrew S.; REDES DE COMPUTADORES. Tradução de Computer networks [3 ed. original] Insight Serviços de Informática - Rio de Janeiro: Campus, 1997. ISBN 85-352-0157-2