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:

   - http://www.praetoriate.com/10g_139.htm

   - http://oraclelon1.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dcitblfnsref.htm

   - http://www.codeguru.com/cpp/data/mfc_database/oracle/article.php/c4285/

   - http://www.databasejournal.com/features/oracle/article.php/2222781
   - http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm#CHDJEGHC