Banco de Dados - Oracle
Oracle - Junções: Condições e Métodos I
Este artigo pretende descrever as diferentes condições de junção que os desenvolvedores podem escrever e os métodos utilizados pelo otimizador para gerar o resultado.
por Vinícius RonconiDurante a construção de consultas SQL é comum surgir a necessidade de extrair informações de diferentes fontes de dados para gerar o resultado. Esta operação, chamada junção, necessita que o desenvolvedor informe os critérios para realizar a junção destas fontes. Estes critérios são definidos na cláusula WHERE de um comando e são chamados de “condições de junção”.
Este artigo pretende descrever as diferentes condições de junção que os desenvolvedores podem escrever e os métodos utilizados pelo otimizador para gerar o resultado.
Como o Oracle realiza as junções
Para resolver um comando com mais de uma fonte de dados, o Oracle, primeiramente, determina a melhor forma de acesso aos dados de cada fonte isoladamente. Em seguida, faz a junção das fontes em pares, mesmo que a consulta possua mais fontes. O Oracle determina o par mais vantajoso para iniciar a junção e, após concluir a primeira operação, busca uma nova fonte para realizar a junção com o resultado anterior. Este processo se repete até que todas as fontes tenham sido utilizadas.
1. SELECT e.first_name, e.last_name, d.department_name, j.job_title
2. FROM employees e, departments d, jobs j
3. WHERE e.department_id = d.department_id
4. AND e.job_id = j.job_id
Listagem SEQ Listagem \* ARABIC 1 - Consulta em várias fontes de dados
Por exemplo, para resolver a consulta definida na Listagem 1, o Oracle analisa a melhor forma de acesso aos dados nas tabelas “employees”, “departments” e “jobs”.,Em seguida, procura a melhor ordem de unir estas informações. Neste exemplo, a melhor ordem encontrada é unir primeiramente as tabelas “jobs” e “employees”. Somente após encontrar o resultado desta junção é que a tabela “departments” será acessada. O Oracle realiza a junção desta tabela com o resultado obtido na junção anterior para gerar o resultado final. A ordem das operações é ilustrada pela Figura 1.
Figura - Ordem de junção
Condições de Junção
As condições de junção são definidas na cláusula WHERE, como qualquer outra condição da consulta, e possuem influência direta sobre a escolha do método de junção que será realizado pelo otimizador. O que as torna diferentes das demais condições, é que elas são utilizadas para realizar a união entre os dados contidos nas diferentes fontes. Para executar a junção, o Oracle combina os registros das fontes, formando um único registro contendo os campos definidos na cláusula SELECT.
Conforme as condições de junção definidas pelos desenvolvedores, é possível obter os tipos de junção:
· Junções simples (simple-join) – A consulta retornará apenas os registros que atendem às condições definidas nas fontes de dados;
· Junções externas (outer join) – Semelhante à junção simples, porém retornará também os registros da fonte de dados externa que não estão presentes na fonte interna;
· Produtos cartesianos – Retorna toda a combinação de registros das duas fontes de dados, uma vez que não existe nenhuma restrição definida na cláusula WHERE.
Métodos de Junção
O método de junção utilizado para resolver uma consulta será definido pelo otimizador do Oracle. Para tomar esta decisão, ele se baseia em algumas características da consulta, como as condições de junção definidas na cláusula WHERE e o custo de acessar as fontes de dados existentes. Fatores físicos, como o espaço destinado para operações hash ou de ordenação dos dados também podem influenciar a escolha do método.
As primeiras verificações realizadas pelo servidor são as existências das constraints UNIQUE e PRIMARY KEY e de condições de junção externa. Caso as constraints estejam presentes, o otimizador busca resolvê-las em primeiro lugar por questões de desempenho. Já as condições de junção externa devem ser resolvidas na ordem determinada pelo usuário para que possam produzir o resultado esperado.
Feitas estas considerações, o otimizador gera um conjunto de planos de execução para buscar aquele que apresenta o menor custo. Os planos serão gerados conforme as formas de acesso aos dados, métodos e ordens de junção disponíveis.
Os métodos de junção existentes são: junções nested loop, junções hash, junções sort merge e produtos cartesianos.
Junções nested loop
Este método de junção é vantajoso para realizar a junção de fontes de dados que contém poucos registros e existe uma relação clara de dependência entre as fontes. Desta forma, o otimizador elege uma fonte principal, que servirá de base para buscar os registros na fonte dependente. Para cada registro existente na fonte principal, o Oracle buscará os registros na fonte dependente que atendam às condições da fonte principal.
O exemplo definido pela Listagem 2 pode ilustrar este método de acesso. O otimizador dividirá este comando em duas consultas menores, sendo a principal representada na Listagem 3.
1. SELECT e.first_name, e.last_name, d.department_name
2. FROM employees e, departments d
3. WHERE e.department_id = d.department_id
4. AND e.employee_id = 200
Listagem SEQ Listagem \* ARABIC 2 - Consulta resolvida com o método nested loop
O resultado desta primeira parte influencia diretamente sobre o resultado da outra consulta. Não faz sentido recuperar informações na tabela “departments” de empregados que não sejam aqueles retornados pela consulta da Listagem 2. Desta forma, é possível verificar claramente a relação de dependência entre as consultas.
1. SELECT e.first_name, e.last_name
2. FROM employees e
3. WHERE e.employee_id = 1
Listagem SEQ Listagem \* ARABIC 3 - Consulta principal
Para cada registro retornado pela consulta principal, o otimizador aplicará a consulta dependente, definida na Listagem 4 para gerar o resultado final.
1. SELECT d.department_name
2. FROM departments d
3. WHERE e.department_id = d.department_id
Listagem SEQ Listagem \* ARABIC 4 - Consulta dependente
O custo de acesso aos dados pode ser definido pela fórmula apresentada na Listagem 5. A estimativa de custos baseia-se nas estatísticas existentes no dicionário de dados.
1. Custo = Custo Acesso FE +
2. (Custo de acesso FI * Quantidade de registros na FE)
3. onde
4. FE – Fonte Externa
5. FI – Fonte Interna
Listagem SEQ Listagem \* ARABIC 5 - Custo de acesso do método nested loop
Caso não exista relação entre as fontes, este método irá recuperar todos os registros da fonte interna para cada registro existente na fonte externa. Nestes casos, a utilização da junção hash apresenta desempenho superior. Além disto, a junção nested loop também se torna ineficiente quando houver muitos registros na fonte principal, pois necessita executar a consulta na fonte dependente muitas vezes. Em geral, quando a fonte principal possui mais de dez mil registros o otimizador utiliza outro método de junção.
Sempre que o operador NOT IN for utilizado, o otimizador escolherá o método nested loop para resolver o comando. Outros operadores que levam o otimizador a escolher este método são EXISTS e IN, quando as sub-consultas definidas não podem ser reescritas pelo transformador de consultas para que façam parte da consulta principal.
Conclusão
Este artigo apresentou as possíveis condições de junção entre as fontes de dados e iniciou o detalhamento dos métodos utilizados pelo Oracle para resolver estas junções, descrevendo o método nested loop. Este é um dos métodos mais utilizados pelo Oracle, uma vez que possui ótimo desempenho quando as fontes possuem uma relação clara entre si e possuem poucos registros.
Os demais métodos de junção (hash, sort merge e produto cartesiano) serão explicados no próximo artigo desta série.
Referências
· http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm
· http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm
