Social Icons

23 de dez. de 2010

Otimizando ETL com Pipelined Table Functions


  
Olá pessoal,
   
    Neste artigo apresentarei um recurso muito bom para otimizar ETL (Extract, Transform, and Load), que existe no Oracle Database desde a versão 9i, e que chama-se Pipelined Table Function (PTF).
    
     PTF permite criar funções que retornam dados como se fossem uma tabela virtual, podendo transformar os dados de retorno enquanto eles são produzidos, ou seja, é possível alterar os dados pesquisados em uma tabela, linha por linha, enquanto eles são processados, sem ter que esperar pelo retorno completo do "resultset" (conjunto de dados retornados pela função).
   
     PTF é ótimo para ETL, pois é rápido e consome menos memória que outros métodos que podem ser utilizados para o mesmo objetivo, como por exemplo, preencher um cursor e percorrê-lo para transformar e retornar dados.
   
     Seguem abaixo 3 scripts que demonstram como criar e testar uma Pipelined Table Function. Os scripts utilizam a tabela EMPLOYEES do schema de exemplo HR. Se você não tem o schema HR instalado em seu Banco de Dados, leia o artigo Instalando o schema de exemplo HR.


------------------------------------------------------------------------------
    Para iniciar o passo-a-passo dos itens abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA) ou com o usuário HR.  
------------------------------------------------------------------------------
     
   1- Criando a package HR.PKG_TYPES  
        A package HR.PKG_TYPES contém os tipos de dados que são criados para retornarem uma tabela virtual na função que será criada no próximo passo:


          create or replace package HR.PKG_TYPES as   
                      TYPE TABLEEMPTYPE IS TABLE OF EMPLOYEES%ROWTYPE;
                      TYPE ROWEMPTYPE IS RECORD (EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE,
                                  FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE,
                                  LAST_NAME EMPLOYEES.LAST_NAME%TYPE,
                                  EMAIL EMPLOYEES.EMAIL%TYPE,
                                  PHONE EMPLOYEES.PHONE_NUMBER%TYPE,
                                  HIRE_DATE EMPLOYEES.HIRE_DATE%TYPE,
                                  JOB_ID EMPLOYEES.JOB_ID%TYPE,
                                  SALARY EMPLOYEES.SALARY%TYPE,                                 
                                  COMMISSION_PCT EMPLOYEES.COMMISSION_PCT%TYPE,
                                  MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE,
                                  DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE
                                  );   
            END;

            /
    
   2- Criando a função HR.FC_OBTER_EMPREGADOS   
        A função HR.FC_OBTER_EMPREGADOS lê e retorna os dados de apenas 4 colunas da tabela HR.EMPLOYEES, transformando os dados das colunas LAST_NAME e EMAIL:


           CREATE OR REPLACE  function HR.FC_OBTER_EMPREGADOS
                         return PKG_TYPES.TABLEEMPTYPE PIPELINED IS
                               var_linha PKG_TYPES.ROWEMPTYPE;
           BEGIN
                   FOR CUR_ROW IN (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL

                                                 FROM    HR.EMPLOYEES)
                   LOOP
                         var_linha.EMPLOYEE_ID:=CUR_ROW.EMPLOYEE_ID;
                         var_linha.FIRST_NAME:=CUR_ROW.FIRST_NAME;
                         var_linha.LAST_NAME:=UPPER(CUR_ROW.LAST_NAME);
                         var_linha.EMAIL:= UPPER(cur_row.EMAIL ||
'@ORACLE.COM');
 
                        PIPE ROW(VAR_LINHA);
                  END LOOP;
        

                  RETURN;
           END;

           /


         Obs.: A instrução PIPE ROW retorna os resultados para a sessão de usuário Oracle, linha por linha. Isso otimiza o tempo de resposta da aplicação.
     

   3- Testando a função HR.FC_OBTER_EMPREGADOS    
         A query abaixo retorna os dados da função HR.FC_OBTER_EMPREGADOS, como se fossem uma tabela virtual:


        SELECT * FROM TABLE(HR.FC_OBTER_EMPREGADOS);  
 

Observações:
     O exemplo deste artigo foi criado de forma simples apenas para demonstrar o uso de PTFs. Para mais informações e exemplos deste tipo de função, consulte as referências no final deste artigo ou o treinamento PL/SQL Essentials, módulo PL/SQL Tuning. 

Comentários Finais:
     Apesar das PTFs serem trabalhosas para criar, elas são ótimas para otimizar performance de queries complexas que utilizam funções de transformação para alterar os valores originais das consultas.
  
     Na empresa em que trabalho temos um caso de uma query que demorava 54 segundos para gerar os dados da folha de ponto mensal de cada empregado. Essa query estava literalmente "parando" o servidor de Banco de Dados. Eu orientei o desenvolvedor da query a alterá-la utilizando a Cláusula WITH + Pipelined Table Function. Após as alterações o tempo de execução da query caiu para 0.4 segundos.
   
  
Referências:

10 comments:

  1. Olá, estava fazendo um teste para verificar como funciona o retorno de consultas utilizando Pipelined Table Functions, para isto fiz o seguinte, criei a package PKG_TYPES no meu banco de teste:

    CREATE OR REPLACE PACKAGE PKG_TYPES AS
    TYPE TABLEPLCTYPE IS TABLE OF PLANOCONTA%ROWTYPE;
    TYPE ROWPLCTYPE IS RECORD (IDPLANOCONTA PLANOCONTA.IDPLANOCONTA%TYPE);
    END;

    Depois criei a Função FC_OBTER_RAZAO_CONTABIL:

    CREATE OR REPLACE FUNCTION FC_OBTER_RAZAO_CONTABIL
    RETURN PKG_TYPES.TABLEPLCTYPE PIPELINED IS
    VAR_LINHA PKG_TYPES.ROWPLCTYPE;
    BEGIN
    FOR CUR_ROW IN (SELECT IDPLANOCONTA FROM PLANOCONTA)
    LOOP
    VAR_LINHA.IDPLANOCONTA:=CUR_ROW.IDPLANOCONTA;
    PIPE ROW(VAR_LINHA);

    END LOOP;
    RETURN;
    END;

    A package compila sem problemas, mas a function me retorna o seguinte erro ao compilar:

    Compilation errors for FUNCTION AFBWEBS1.FC_OBTER_RAZAO_CONTABIL

    Error: PLS-00382: expression is of wrong type
    Line: 8
    Text: PIPE ROW(VAR_LINHA);

    Alguém saberia me dizer o que pode estar acontecendo para retornar este erro de compilação?

    ResponderExcluir
    Respostas
    1. Anônimo, olhando o seu código não encontrei nada de errado. Veja se o link https://community.oracle.com/thread/1033426 te ajuda.

      []s

      Excluir
  2. Anonimo, o código parece estar correto. VC criou a package no mesmo schema da function?

    ResponderExcluir
  3. Olá Fábio, estou com o mesmo problema citado pelo Anônimo.
    Estão no mesmo schema.

    ResponderExcluir
    Respostas
    1. Carlos veja se o link https://community.oracle.com/thread/1033426 te ajuda.

      Excluir
  4. Respostas
    1. Bruno, veja se o link https://community.oracle.com/thread/1033426 te ajuda.

      Excluir
  5. Bom dia é possível consultar o que já foi retornado pelo comando PIPE ROW? Estou com um problema onde uso uma função recursiva e precisava saber se algum momento valor já foi retornado. Imagem que se a PIPE ROW populasse uma tabela eu gostaria de consulta o que já foi incluído antes de terminar

    ResponderExcluir
    Respostas
    1. Olá André, não entendi muito bem o que você quer fazer, mas acho que se você quis dizer que quer ver um determinado valor antes de retorná-lo, sim você pode fazer isso dentro da função PTF, acrescentando por exemplo um "IF VAR_LINHA." dentro do LOOP do código de exemplo da função FC_OBTER_RAZAO_CONTABIL deste artigo.

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)