O artigo anterior apresentou o que são as junções, quais os tipos de condições de junção que podem ser definidos pelos usuários e iniciou a explicação sobre os métodos de junção utilizados pelo Oracle. O método nested loop foi descrito, indicando as situações em que sua utilização é vantajosa para o otimizador.

Este artigo detalhará os demais métodos utilizados pelo Oracle para realizar a junção entre diferentes fontes de dados declaradas pelo usuário na cláusula FROM de uma consulta.

Junções hash

Este é o método de junção preferido pelo otimizador do Oracle para realizar a junção entre fontes de dados com muitos registros ou em situações onde a maior parte dos dados das fontes será retornada para gerar o resultado.

Por exemplo, a consulta definida na Listagem 1 será resolvida através de junção hash. Apesar de haver uma relação clara de dependência entre as consultas, o otimizador entende que esta consulta principal precisará utilizar uma grande fração dos dados. Isto acontece por causa da utilização de função no campo “last_name”. Outro motivo que poderá levar o otimizador a escolher o método hash ao invés do método nested loop é a ausência de estatísticas sobre as fontes de dados.

Listagem 1 - Resultado gerado através de hash

Além disto, este método somente será utilizado quando a junção entre as fontes utilizar o operador de igualdade. Não é possível utilizar o método junção hash em conjunto com os operadores <, <=, > ou >=.

Nesse método, o otimizador escolhe a menor fonte de dados para construir uma tabela hash na memória, utilizando os campos que compõem a chave de junção. Esta chave é composta pelos campos utilizados para relacionar as fontes envolvidas na operação. Em seguida, o otimizador busca na outra fonte de dados (a maior delas) para recuperar os registros que atendem às condições de junção com os dados da tabela hash.

Uma tabela hash é uma estrutura criada para representar um mapeamento de uma chave para um valor na memória. As chaves são obtidas através da aplicação de uma função que gere o valor hash nos dados que serão armazenados nesta tabela. Ao lado das chaves serão armazenados os valores.

A estrutura hash requer mais memória do que outras estruturas criadas para armazenar conjuntos de valores. Entretanto, nesta estrutura o tempo de resposta é constante, apresentando, portanto, desempenho superior às demais formas de armazenamento. Este método apresenta melhores resultados quando é possível colocar todos os registros da menor fonte de dados na memória. Com isto, a tabela hash possuirá apenas uma única partição, limitando o custo de acesso a uma única leitura. Se isto não for possível, o otimizador dividirá a tabela em partições.

Após concluir a construção da tabela hash, o otimizador busca os dados na outra fonte de dados, dividindo-a em partições, tal como aconteceu com a primeira fonte. Para cada partição da tabela hash a partição correspondente da maior fonte de dados será carregada para a memória e os registros que atendem às condições de junção serão retornados para gerar o resultado da consulta.

O custo desta operação será de duas leituras (uma na tabela hash e outra na maior fonte de dados) e uma de gravação (para gerar o resultado) para cada partição criada. O custo para operações de junção hash pode ser representado pela fórmula apresentada na Listagem 2.

Listagem 2 - Custo de acesso da junção hash

Junções sort merge

Quando não houver relação de dependência entre as fontes de dados, o otimizador não poderá utilizar o método nested loop. Ele avalia então os demais métodos existentes, sendo que para as junções de igualdade, o método junção hash costuma apresentar os melhores resultados. Porém, existe uma situação específica onde o método sort merge apresenta melhores resultados: se as fontes de dados já estiverem ordenadas e a consulta não precisar realizar alguma ordenação específica, o otimizador utilizará, então, o método sort merge para resolver a consulta.

Além desta situação específica, este método também é utilizado para resolver os comandos com condições de diferença, como aqueles que utilizam os operadores <, <=, > ou >=.

Para resolver a junção utilizando este método, o otimizador organiza as duas fontes de dados pelos campos da junção. Esta operação somente será necessária se os dados não estiverem organizados. Em seguida o otimizador faz a junção das fontes organizadas.

Outros fatores que podem levar o otimizador a escolher o método sort merge em detrimento do método hash são:

· O parâmetro HASH_JOIN_ENABLED possui o valor “false”. Este valor desabilita o método junção hash;

· As áreas reservadas para hash e operações para ordenar os dados são insuficientes para armazenar os dados em uma única partição. Com isto, o desempenho do método hash seria degradado.

Junções cartesianas

Figura 1 - Produto Cartesiano

Este método de junção será utilizado quando não for definida nenhuma condição de junção entre as fontes de dados da consulta. Desta forma, o resultado será a combinação de todos os registros das fontes de dados, como ilustrado na Figura 1.

Em geral, este tipo de junção acontece quando a consulta possui algum erro, e as condições de junção entre as fontes não foram corretamente definidas, como o exemplo definido na Listagem 3. Neste exemplo, o comando não possui nenhuma condição de junção entre as fontes de dados “employees” e “departments”. Desta maneira, para cada registro existente na fonte “employees” serão retornados todos os registros da fonte “departments”.

Listagem 3 - Consulta sem as condições de junção

Para corrigir o problema é necessário informar quais campos da fonte de dados “employees” devem ser ligados à fonte “departments” para criar uma relação entre as fontes. Para isto, deve-se adicionar a cláusula WHERE definida na Listagem 4.

Listagem 4 - Condição de junção entre as fontes EMPLOYEES e DEPARTMENTS

Em consultas com poucas fontes de dados, é uma tarefa fácil identificar as condições de junção. Mas consultas que utilizam muitas fontes de dados exigem mais atenção dos desenvolvedores para garantir que todas as condições foram informadas corretamente.

Como este método recupera todas as informações das fontes de dados, realizando uma leitura completa em uma fonte para cada registro existente na outra, o desempenho de comandos que executam este tipo de junção será muito ruim, piorando à medida que o tamanho das fontes de dados aumenta.

Conclusão

Esta série apresentou o que são as junções, quais as formas que os desenvolvedores possuem para definir o tipo de junção que será realizado e os métodos utilizados pelo otimizador do Oracle para resolver estas junções. Cada método foi detalhado, apresentando as situações em que cada método apresenta vantagens para resolver um comando.

A partir destas informações, o desenvolvedor de aplicações passa a ter subsídios para identificar possíveis gargalos na execução das consultas e buscar formas alternativas de construir suas consultas.

Referências

· http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm