Queries hierárquicas no Oracle Database

ATUALIZADO EM 11/07/2016

Olá pessoal,

  

     No artigo de hoje veremos como escrever queries hierárquicas, através da cláusula CONNECT BY, que existe no Oracle Database, se não me engano desde a versão 8, e que é um recurso bastante rápido e fácil de se utilizar (comparando-a com as outras opções existentes, que iremos ver ao longo do artigo)!

     Para entendê-lo, vamos analisar na Figura 1, uma árvore hierárquica de cargos de empregados em uma organização. O cargo mais alto (Presidente) está no topo da árvore e os cargos inferiores se ramificam em níveis abaixo do topo, de acordo com a hierarquia dos cargos. Na Figura 1, os números de 1 à 4, em vermelho, indicam o nível do cargos dentro da hierarquia.

Figura 1. Árvore hierárquica de cargos

     Essa representação hierárquica de cargos é uma representação dos cargos que existem na tabela de empregados (EMPLOYEES) do schema de exemplo HR. Para conhecer ou instalar o schema HR leia o artigo Instalando o schema de exemplo HR.

     Antes de escrever uma consulta hierárquica, vamos conhecer primeiro as principais palavras-chave que podem ser utilizadas, com a cláusula CONNECT BY:

          - LEVEL: representa o nível da linha retornada dentro da árvore hierárquica;

         - PRIOR: PRIOR + nome de coluna, retorna dados de uma coluna na linha de nível imediatamente acima na hierarquia;
         - START WITH: especifica a linha raiz da hierarquia (onde começar);

         - CONNECT BY PRIOR: especifica as colunas de 1 tabela nas quais existe o relacionamento entre linhas mães e filhas.
    

     Iremos agora ver a consulta que permite retornar todos os empregados da tabela EMPLOYEES, ordenados hierarquicamente pelo cargo mais alto (TOP DOWN). A consulta irá retornar id, nome, cargo e nível dos cargos dos empregados + nome e cargo dos empregados de nível superior imediato:

     SELECT         e.employee_id, 

                  E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE_NAME,
                  J.JOB_TITLE,
                  LEVEL,  -- indica o nível do cargo na hierarquia           
                  PRIOR E.FIRST_NAME || ' ' || PRIOR E.LAST_NAME AS MANAGER_NAME, -- retorna dados do empregado no cargo imediatamente acima na hierarquia                
                  PRIOR J.JOB_TITLE AS MANAGER_JOB                 
    FROM          HR.EMPLOYEES E
    INNER JOIN    HR.JOBS J
        ON        E.JOB_ID = J.JOB_ID
     START WITH   e.manager_id  IS NULL -- indica onde começa a hierarquia     
     CONNECT BY PRIOR  E.EMPLOYEE_ID = E.MANAGER_ID; -- indica relacionamento da hierarquia (neste caso, TOP DOWN)

    Veja o resultado da consulta na Figura 2. Nela podemos verificar que o cargo mais alto, o de Presidente (nível 1), é ocupado pelo empregado Steven King. Em um nível imediatamente abaixo (nível 2), temos 5 cargos. É possível ainda, ver na imagem, o cargo de Vice-Presidente Administrativo ocupado por Neena Kochar, e abaixo dela, vários outros cargos que irão preencher toda a árvore hierárquica.
  

Figura 2.  Executando queries hierárquicas

    Outra forma de retornarmos o resultado acima, seria escrevendo SQL padrão ANSI/ISO/IEC, com CTE  (Commom Table Expression, popularmente conhecida como cláusula WITH) recursivo, como no exemplo abaixo:

WITH t1(employee_id, EMPLOYEE_NAME, JOB_TITLE, "LEVEL", MANAGER_NAME, MANAGER_JOB, manager_id) AS 
  (
   select       e.employee_id, 
                E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE_NAME, 
                J.JOB_TITLE,
                1 AS "LEVEL",
                NULL AS MANAGER_NAME,
                NULL AS MANAGER_JOB,
                e.manager_id
   FROM         HR.EMPLOYEES E
   INNER JOIN   HR.JOBS J
       ON       E.JOB_ID = J.JOB_ID
   where        e.manager_id  IS NULL 
  UNION ALL
  select        e.employee_id, 
                E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE_NAME, 
                J.JOB_TITLE,
                "LEVEL"+1, 
                T1.EMPLOYEE_NAME AS MANAGER_NAME,
                T1.JOB_TITLE AS MANAGER_JOB,
                e.manager_id
   FROM         HR.EMPLOYEES E
   INNER JOIN   HR.JOBS J
       ON       E.JOB_ID = J.JOB_ID
   INNER JOIN   T1
       ON       E.MANAGER_id = T1.EMPLOYEE_ID   
  )
SEARCH DEPTH FIRST BY employee_id SET order1
SELECT      employee_id, 
            EMPLOYEE_NAME, 
            JOB_TITLE,
            "LEVEL",
            MANAGER_NAME,
            MANAGER_JOB            
FROM t1
ORDER BY order1;

   
     Pergunta 1: se há 2 formas de se fazer a mesma coisa, qual delas devo usar? Resposta: Bom... a 1ª é mais simples e mais rápida, mas é específica do Oracle e você terá que reescrevê-la se quiser fazer a mesma coisa em outro SGBD. A 2ª, apesar de ser menos performática e mais "longa" de se escrever, ela tem a vantagem de ser padrão ANSI, ou seja, é um padrão SQL que funcionará na última versão de qualquer um dos SGBDs relacionais mais utilizados e conhecidos do mercado.

     Pergunta 2: Como eu sei que o padrão Oracle é mais rápido que o padrão ANSI? Resposta: Veja abaixo você mesmo, o plano de execução dos 2 SQLs:

   

Figura 3: Plano de execução do SQL recursivo utilizando a cláusula CONNECT BY (padrão Oracle)

Figura 4: Plano de execução do SQL com CTE recursivo (padrão ANSI)

Bom pessoal, por hoje é só! 

Procurei abordar neste artigo um exemplo prático e fácil de entender. É importante ressaltar que em queries hierárquicas é possível ainda utilizar vários outros recursos, como por exemplo: filtrar níveis da hierárquia e montar a árvore de baixo para cima (ao invés de cima para baixo, como foi demonstrado no exemplo acima).

 

 

[]s

 

        

Referências:

   - Treinamento oficial Oracle: "Banco de Dados Oracle 10g: Fundamentos de SQL II"