Métodos de Joins no Oracle Parte II
Pessoal dando continuidade no assunto hoje vou falar de Sort merge joins.
Em uma operação MERGE JOIN, as entradas para o join são tratadas separadamente, classificadas e unidas. O MERGE JOIN geralmente é usado quando não há índices disponíveis para as condições limitantes da consulta. Em um Sort-Merge join, o Oracle classifica a primeira linha por suas colunas de join, classifica novamente a segunda linha pelas colunas do join e então faz um merge das classificações das linhas. Os resultados encontrados são colocados no conjunto de resultados.
Merges JOINS podem ser usados para unir linhas de duas tabelas independentes, Hash Joins geralmente possuem desempenho melhor que MERGE JOINS. Por outro lado, SORT MERGE Joins (Merge Joins) podem ter um desempenho melhor que hash joins se as seguintes condições existem:
- As linhas já foram ordenadas.
- Não são necessárias operações de classificação.
- No entanto se um tipo junção por merge join envolve a escolha de um método de acesso mais lento (um index scan ao invés de um FULL TABLE SCAN) então a vantagem de usar um MERGE JOIN pode ser perdida.
Em um MERGE JOIN, não existe o conceito de uma tabela de condução. O join é constituído de duas etapas. Merges joins são caracterizados pelas seguintes propriedades:
- Cada operação filha é executada apenas uma vez.
- Ambas as entradas devem ser classificados de acordo com as colunas da condição de join.
- Devido às operações de classificação, ambas as entradas devem ser lidas na íntegra e classificadas antes de retornar a primeira linha do conjunto de resultados.
- Todos os tipos de joins são suportados.
Sort-merge joins podem ser eficazes quando ocorre a falta de dados seletivos ou quando usam índices úteis em joins com NESTED LOOPS ineficientes, ou quando as duas fontes de dados são grandes (superiores a 5 por cento dos blocos acessados). No entanto, Sort-Merge joins só podem ser utilizados para equijoins (WHERE =D.deptno = E.deptno, ao contrário WHERE D.deptno >= E.deptno).
Sort-merge joins precisam de segmentos temporários para classificação .
Isso pode levar a utilização de memória extra e/ou I/O extra na tablespace temporária.
Vamos analisar o plano de execução dessa consulta envolvendo duas tabelas:
SELECT /*+ ordered use_merge(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1180 | 28 (8)| 00:00:01 |
| 1 | MERGE JOIN | | 10 | 1180 | 28 (8)| 00:00:01 |
| 2 | SORT JOIN | | 1 | 57 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 57 | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 100 | 6100 | 25 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 100 | 6100 | 24 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")A operação MERGE JOIN é do tipo independente. Isso significa que as operações filhas são processadas uma única vez e de forma independente uma do outra.
Neste caso, o processamento do plano de execução pode ser resumido da seguinte forma:
- Todas as linhas na tabela t1 são lidas através de um FULL TABLE SCAN, a restrição n = 19 é aplicada, e as linhas resultantes são classificadas de acordo com as colunas usadas como a condição de junção (id).
- Todas as linhas da tabela t2 são lidas através de um FULL TABLE SCAN e classificados de acordo com as colunas usadas como a condição de junção (t1_id).
- As duas tabelas são unidas juntas e as linhas resultantes são retornadas. Note-se que a associação em si é simples, porque os dois conjuntos de dados são classificados de acordo com o mesmo valor (as colunas usadas na condição de join).
A limitação mais importante de uma operação MERGE JOIN é a sua incapacidade para tomar vantagem dos índices para aplicar nas condições de join.
Em outras palavras, os índices podem ser usados apenas como um caminho de acesso para avaliar as restrições (se disponível) antes de classificar as entradas. Por exemplo, se a restrição n = 19 fornece boa seletividade, pode ser útil para criar um índice para aplicá-la.
SQL > CREATE INDEX t1_n em T1 (n)Na verdade, com esse índice em vigor, o plano de execução a seguir pode ser visualizado.
Você deve notar que a tabela T1 já não é acessada através de um FULL TABLE SCAN.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1180 | 28 (8)| 00:00:01 |
| 1 | MERGE JOIN | | 10 | 1180 | 28 (8)| 00:00:01 |
| 2 | SORT JOIN | | 1 | 57 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 57 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 100 | 6100 | 25 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 100 | 6100 | 24 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")Para executar Merge Joins, uma quantidade não desprezível de recursos podem ser gastos em operações de classificação. Para melhorar o desempenho, o otimizador de consultas evita a realização de operações de classificação sempre que economiza recursos. Mas, é claro, isso só é possível quando os dados já estão classificados de acordo com as colunas usadas como a condição de join.
Isto acontece em duas situações.
A primeira é quando um index rande scan se aproveita de um índice construído sobre as colunas usadas para a condição de join. A segunda é quando a etapa anterior ao merge join (por exemplo, outro merge join) já possui os dados classificados na ordem correta. Por exemplo, na execução do seguinte plano, observe como a tabela T1 é acessada através do indice t1_pk (que é construída sobre a coluna id utilizada como condição de join). Como resultado, para a entrada do lado esquerdo, a operação de ordenação (SORT JOIN) pode ser evitada.
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
|*2 | TABLE ACCESS BY INDEX ROWID | T1 |
| 3 | INDEX FULL SCAN | T1_PK |
|*4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL | T2 |
----------------------------------------------
2 - filter("T1"."N"=19)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")Quando uma operação MERGE JOIN é usada para juntar dois conjuntos de registros, cada conjunto de registros é processado separadamente antes de serem unidos. A operação MERGE JOIN não pode começar até que ele tenha os dados recebidos de ambas às operações de SORT JOIN que contribuem para isso. O SORT JOIN por sua vez, não vai fornecer dados para o MERGE JOIN até que todas as linhas tenham sido classificadas. Se os índices são usados como fontes de dados, o SORT JOIN pode ser ignorado.
Se o MERGE JOIN tem que esperar duas operações SORT JOIN separadas para ser concluída, uma junção que usa o MERGE JOIN tipicamente terá um fraco desempenho para usuários online. A percepção do mau desempenho é devido ao atraso na devolução da primeira linha da associação para os usuários. Com o aumento de tamanho das tabelas, o tempo requerido para a classificação ser concluída aumenta drasticamente.
Se as tabelas são grandes e com um tamanho desigual, então a operação de triagem realizada na tabela maior irá impactar negativamente o desempenho da consulta geral.
As Operações de MERGE JOIN envolvem uma leitura completa (FULL SCAN) e classificação das tabelas envolvidas, você só deve usar MERGE JOIN se ambas as tabelas são muito pequenas ou se ambas as tabelas são muito grandes. Se ambas as tabelas são muito pequenas, então o processo de varredura e classificação das tabelas será concluído rapidamente. Se ambas as tabelas são muito grandes, então as operações de classificação e a varredura exigem que as operações de MERGE JOIN possam aproveitar as opções de paralelismo do Oracle.
O Oracle pode paralelizar operações, permitindo que múltiplos processadores possam participar da execução de um único comando. Entre as operações que podem ser paralelizadas são os TABLE ACCESS FULL e operações de classificação. Desde que um MERGE JOIN use o TABLE ACCESS FULL e operações de classificação, ele pode tirar proveito de opções de paralelismo do Oracle. Consultas Paralelizadas envolvendo operações de MERGE JOIN freqüentemente melhoram o desempenho das consultas (desde que haja adequado recursos de sistema disponíveis para apoiar as operações em paralelo).
No próximo post vou falar sobre HASH JOINS.
