Depois muito tempo sem freqüentar e atualizar o blog , neste ano pretendo ser mais ativo na comunidade ,tendo tirado o ano passado para novas experiências profissionais infelizmente não tive tempo de aparecer por aqui, porém agora podem esperar novas atualizações.
E volto falando de um assunto que sempre me perguntam que é como o oracle realiza joins com mais de uma tabela.
NESTED LOOPS (Loops aninhados)
Definição
Os registros da primeira tabela são recuperados, e para cada registro recuperado, o acesso é realizado na segunda tabela.
NESTED LOOPS são úteis quando pequenos subconjuntos de dados estão sendo juntados, e se a condição de join é uma forma eficiente de acesso à segunda tabela.
O acesso da segunda tabela é feito através de um acesso baseado em índice (index-based access).
Um hint é usado para recomendar um acesso baseado em índice da tabela Livros no exemplo abaixo:
select /*+ INDEX(Livros) */
Livros.Publisher,
Livros_autor.AuthorName
from Livros, Livros_autor
where Livros.Title = Livros_autor.Title;
A coluna title da tabela Livros é utilizada como parte da condição de join na consulta, o índice de chave primária pode resolver esse join.
Quando a consulta é executada, uma operação de NESTED LOOP pode ser usada para executar esse join.
Para executar um join de NESTED LOOP, o otimizador deve primeiro selecionar uma tabela de condução da junção, que é a tabela que será lida primeiro (geralmente através de uma operação de TABLE ACCES FULL, embora index scans sejam muito utilizados). Para cada registro na tabela de condução, a segunda tabela na junção será consultada. A consulta de exemplo junta as tabelas Livros_autor e Livros, com base nos valores da coluna de title. Durante a execução de NESTED LOOPS, uma operação irá selecionar todos os registros da tabela Livros_autor. O índice de chave primária da tabela Livros será investigado para determinar se ele contém uma entrada para o valor do registro atual da tabela Livros_autor. Se uma correspondência for encontrada, então o valor de Title será retornado da chave primária de Livros. Se outras colunas são necessárias para Livros, a linha será selecionada da tabela Livros através de uma operação de TABLE ACCESS BY INDEX ROWID.
Então podemos resumir que um NESTED LOOP envolve as seguintes etapas:
1_ O otimizador determina a tabela de condução e designa-o como uma tabela externa.
2_ A outra tabela é designada como a tabela interna.
Para cada linha na tabela externa, o Oracle permite acesso a todas as linhas na tabela interna.
O outer loop é para cada linha na tabela externa e o inner loop é para todas as linhas na tabela interna.
O outer loop aparece antes do inner no plano de execução, como segue:
Agora vamos mostrar o plano de execução desta consulta para esclarecer a sua mente.
EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
FROM employees e, jobs j, departments d
WHERE e.employee_id < 103
AND e.job_id = j.job_id
AND e.department_id = d.department_id;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 165 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 165 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 3 | 123 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 48 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 25 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 6 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 14 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPLOYEE_ID"<103)
6 - access("E"."JOB_ID"="J"."JOB_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Neste outro exemplo, o outer loop recupera todas as linhas da tabela de employees.
Para cada linha da tabela employees recuperada pelo outer loop, o inner loop recupera a linha associada na tabela jobs.
Outer loop
Nesse exemplo a consulta abaixo seria equivalente ao que o outer loop executa com uma tabela de conducao.
SELECT e.employee_id, e.salary
FROM employees e
WHERE e.employee_id < 103;
Inner loop
O plano de execução mostra o inner loop sendo iterado para cada linha obtida
a partir do outer loop, como segue:
SELECT j.job_title
FROM jobs j
WHERE e.job_id = j.job_id ;
É muito importante garantir que a tabela interna é conduzida a partir da da tabela externa.
Se o caminho de acesso a tabela interna é independente da tabela externa e em seguida as mesmas linhas são recuperadas para cada iteração de outer loop o desempenho vai cair consideravelmente.
Em tais casos, operaçoes de *hash joins juntam duas fontes independentes com um melhor desempenho.
*Hash join será abordado brevemente
Em operações de acesso a pelo menos duas fontes de dados que estão envolvidos joins com um NESTED LOOPS, vai ocorrer um acesso na tabela de condução(tabela principal) e um acesso normalmente baseado em índice da tabela dirigida .
Os métodos de acesso de dados mais comuns usados são o TABLE ACCESS FULL, TABLE ACCESS BY INDEX ROWID e INDEX-SCAN eles retornam os registros para às operações sucessivas logo que um registro for encontrado, eles não esperam por todo o conjunto de registros selecionados. Porque essas operações podem fornecer o primeiro conjunto de linhas rapidamente para os usuários, Nested Loops são comumente usados para joins que são freqüentemente executados por usuários online.
Ao implementar joins com NESTED LOOPS, você precisa considerar o tamanho da tabela de condução. Se a tabela de condução é grande e é lida através de um FULL TABLE SCAN, então o FULL TABLE SCAN realizado pode afetar negativamente o desempenho da consulta. Se os índices estão disponíveis em ambos os lados do join o Oracle vai selecionar uma tabela de condução para a consulta e o otimizador irá verificar a estatísticas do tamanho das tabelas e a *seletividade dos índices para escolher o caminho com o menor custo global.
* A seletividade é um valor entre 0 e 1 que representa a fração de linhas filtradas por uma operação.
Por exemplo, se uma operação de leitura lê 120 linhas de uma tabela e depois de aplicar um filtro(where) retorna 18 linhas, a seletividade é de 0,15 (18/120). A seletividade também pode ser expressa como um percentual de 0,15 que é expresso como 15 por cento.
Ao juntar três tabelas, o Oracle realiza dois joins distintos: O join de duas tabelas para gerar um conjunto de registros e em seguida um join entre o conjunto de registros e a terceira tabela.
Se NESTED LOOP são utilizados a ordem na qual as tabelas são unidas é crítica.
A saída do primeiro join gera um conjunto de registros e esse conjunto de registros é usado como uma tabela de condução da segunda junção.
O Tamanho do conjunto de registros retornado pelo primeiro join impacta no desempenho do segundo join e assim tem um impacto significativo sobre o desempenho da consulta geral. Você deve tentar unir as tabelas mais seletivas primeiro para que o impacto desses proximos joins sejam insignificantes.
Se tabelas grandes sao unidas no primeir join de uma consulta multijoin, o tamanho das tabelas terá um impacto sucessivos no join e irá impactar negativamente o desempenho global da consulta.
O otimizador deve escolher o caminho adequado para executar o join ,você pode confirmar os custos e o caminho gerado pelo plano de execução.
NESTED LOOPS são úteis quando se criam joins com tabelas de tamanhos diferente assim você pode usar a menor tabela como a tabela de condução e selecione na tabela maior com um acesso baseados em índices.
Quanto mais seletivo o índice, mais rápido a consulta será concluída.
Um join com quatro Tabelas
O plano de execução que se segue é um exemplo de uma árvore típica de profundidade à esquerda, implementado com NESTED LOOP .
Observe como cada tabela é acessada por meio de índices.
O exemplo também mostra como forçar um loop aninhado usando hints ordered e use_nl.
O exemplo especifica o acesso as tabelas na mesma ordem em que aparecem na cláusula FROM.
O último especifica qual método join é usado para juntar as outras tabelas para a primeira tabela .
SELECT /*+ ordered use_nl(t2 t3 t4) */ t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 238K| 1218 (1)| 00:00:15 |
| 1 | NESTED LOOPS | | 1000 | 238K| 1218 (1)| 00:00:15 |
| 2 | NESTED LOOPS | | 100 | 18000 | 116 (0)| 00:00:02 |
| 3 | NESTED LOOPS | | 10 | 1180 | 12 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 57 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 610 | 10 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T2_T1_ID | 10 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | T3 | 10 | 620 | 11 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | T3_T2_ID | 10 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | T4 | 10 | 640 | 11 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | T4_T3_ID | 10 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=19)
7 - access("T1"."ID"="T2"."T1_ID")
9 - access("T2"."ID"="T3"."T2_ID")
11 - access("T3"."ID"="T4"."T3_ID")
O Processamento deste tipo de plano de execução pode ser resumido da seguinte forma
1.A primeira linha é obtida , não quando a consulta é analisada ou executada, o processamento começa por obter a primeira linha que atende o filtro t1.n = 19 da tabela t1.
2.Com base nos dados encontrados na tabela t1, a tabela t2 é procurada. Perceba que o Oracle aproveita a condição do join t1.id = t2.t1_id para acessar a tabela t2. Na verdade, nenhuma restrição é aplicada a essa tabela. Apenas a primeira linha que atende a condição do join é restituída à operação principal.
3.Com base nos dados encontrados na tabela t2, a tabela t3 é procurada. Também neste caso, o oracle tira proveito de uma condição de join, t2.id = t3.t2_id, para acessar a tabela T3. Apenas a primeira linha que preenche a condição de join é devolvida para a operação pai.
4.Com base nos dados encontrados na tabela t3, a tabela t4 é procurada. Aqui novamente o Oracle se aproveita de uma condição de join, t3.id = t4.t3_id, para acessar a tabela T4. A primeira linha que cumpre a condição de join é imediatamente devolvida ao cliente.
5.Quando as linhas subseqüentes são buscadas, as mesmas ações são realizadas para as buscar as seguintes.
Obviamente, o processo é reiniciado a partir da posição do ultimo resultado (que poderia ser a segunda linha que corresponde na tabela t4, se houvesse). É essencial destacar que os dados são devolvidos assim que uma linha que atende à solicitação é encontrada. Em outras palavras, não é necessária à plena execução do join antes de retornar a primeira linha.
No próximo post eu vou abordar como os MERGE JOIN são realizados.
E volto falando de um assunto que sempre me perguntam que é como o oracle realiza joins com mais de uma tabela.
NESTED LOOPS (Loops aninhados)
Definição
Os registros da primeira tabela são recuperados, e para cada registro recuperado, o acesso é realizado na segunda tabela.
NESTED LOOPS são úteis quando pequenos subconjuntos de dados estão sendo juntados, e se a condição de join é uma forma eficiente de acesso à segunda tabela.
O acesso da segunda tabela é feito através de um acesso baseado em índice (index-based access).
Um hint é usado para recomendar um acesso baseado em índice da tabela Livros no exemplo abaixo:
select /*+ INDEX(Livros) */
Livros.Publisher,
Livros_autor.AuthorName
from Livros, Livros_autor
where Livros.Title = Livros_autor.Title;
A coluna title da tabela Livros é utilizada como parte da condição de join na consulta, o índice de chave primária pode resolver esse join.
Quando a consulta é executada, uma operação de NESTED LOOP pode ser usada para executar esse join.
Para executar um join de NESTED LOOP, o otimizador deve primeiro selecionar uma tabela de condução da junção, que é a tabela que será lida primeiro (geralmente através de uma operação de TABLE ACCES FULL, embora index scans sejam muito utilizados). Para cada registro na tabela de condução, a segunda tabela na junção será consultada. A consulta de exemplo junta as tabelas Livros_autor e Livros, com base nos valores da coluna de title. Durante a execução de NESTED LOOPS, uma operação irá selecionar todos os registros da tabela Livros_autor. O índice de chave primária da tabela Livros será investigado para determinar se ele contém uma entrada para o valor do registro atual da tabela Livros_autor. Se uma correspondência for encontrada, então o valor de Title será retornado da chave primária de Livros. Se outras colunas são necessárias para Livros, a linha será selecionada da tabela Livros através de uma operação de TABLE ACCESS BY INDEX ROWID.
Então podemos resumir que um NESTED LOOP envolve as seguintes etapas:
1_ O otimizador determina a tabela de condução e designa-o como uma tabela externa.
2_ A outra tabela é designada como a tabela interna.
Para cada linha na tabela externa, o Oracle permite acesso a todas as linhas na tabela interna.
O outer loop é para cada linha na tabela externa e o inner loop é para todas as linhas na tabela interna.
O outer loop aparece antes do inner no plano de execução, como segue:
Agora vamos mostrar o plano de execução desta consulta para esclarecer a sua mente.
EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
FROM employees e, jobs j, departments d
WHERE e.employee_id < 103
AND e.job_id = j.job_id
AND e.department_id = d.department_id;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 165 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 165 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 3 | 123 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 48 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 25 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 6 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 14 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPLOYEE_ID"<103)
6 - access("E"."JOB_ID"="J"."JOB_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Neste outro exemplo, o outer loop recupera todas as linhas da tabela de employees.
Para cada linha da tabela employees recuperada pelo outer loop, o inner loop recupera a linha associada na tabela jobs.
Outer loop
Nesse exemplo a consulta abaixo seria equivalente ao que o outer loop executa com uma tabela de conducao.
SELECT e.employee_id, e.salary
FROM employees e
WHERE e.employee_id < 103;
Inner loop
O plano de execução mostra o inner loop sendo iterado para cada linha obtida
a partir do outer loop, como segue:
SELECT j.job_title
FROM jobs j
WHERE e.job_id = j.job_id ;
É muito importante garantir que a tabela interna é conduzida a partir da da tabela externa.
Se o caminho de acesso a tabela interna é independente da tabela externa e em seguida as mesmas linhas são recuperadas para cada iteração de outer loop o desempenho vai cair consideravelmente.
Em tais casos, operaçoes de *hash joins juntam duas fontes independentes com um melhor desempenho.
*Hash join será abordado brevemente
Em operações de acesso a pelo menos duas fontes de dados que estão envolvidos joins com um NESTED LOOPS, vai ocorrer um acesso na tabela de condução(tabela principal) e um acesso normalmente baseado em índice da tabela dirigida .
Os métodos de acesso de dados mais comuns usados são o TABLE ACCESS FULL, TABLE ACCESS BY INDEX ROWID e INDEX-SCAN eles retornam os registros para às operações sucessivas logo que um registro for encontrado, eles não esperam por todo o conjunto de registros selecionados. Porque essas operações podem fornecer o primeiro conjunto de linhas rapidamente para os usuários, Nested Loops são comumente usados para joins que são freqüentemente executados por usuários online.
Ao implementar joins com NESTED LOOPS, você precisa considerar o tamanho da tabela de condução. Se a tabela de condução é grande e é lida através de um FULL TABLE SCAN, então o FULL TABLE SCAN realizado pode afetar negativamente o desempenho da consulta. Se os índices estão disponíveis em ambos os lados do join o Oracle vai selecionar uma tabela de condução para a consulta e o otimizador irá verificar a estatísticas do tamanho das tabelas e a *seletividade dos índices para escolher o caminho com o menor custo global.
* A seletividade é um valor entre 0 e 1 que representa a fração de linhas filtradas por uma operação.
Por exemplo, se uma operação de leitura lê 120 linhas de uma tabela e depois de aplicar um filtro(where) retorna 18 linhas, a seletividade é de 0,15 (18/120). A seletividade também pode ser expressa como um percentual de 0,15 que é expresso como 15 por cento.
Ao juntar três tabelas, o Oracle realiza dois joins distintos: O join de duas tabelas para gerar um conjunto de registros e em seguida um join entre o conjunto de registros e a terceira tabela.
Se NESTED LOOP são utilizados a ordem na qual as tabelas são unidas é crítica.
A saída do primeiro join gera um conjunto de registros e esse conjunto de registros é usado como uma tabela de condução da segunda junção.
O Tamanho do conjunto de registros retornado pelo primeiro join impacta no desempenho do segundo join e assim tem um impacto significativo sobre o desempenho da consulta geral. Você deve tentar unir as tabelas mais seletivas primeiro para que o impacto desses proximos joins sejam insignificantes.
Se tabelas grandes sao unidas no primeir join de uma consulta multijoin, o tamanho das tabelas terá um impacto sucessivos no join e irá impactar negativamente o desempenho global da consulta.
O otimizador deve escolher o caminho adequado para executar o join ,você pode confirmar os custos e o caminho gerado pelo plano de execução.
NESTED LOOPS são úteis quando se criam joins com tabelas de tamanhos diferente assim você pode usar a menor tabela como a tabela de condução e selecione na tabela maior com um acesso baseados em índices.
Quanto mais seletivo o índice, mais rápido a consulta será concluída.
Um join com quatro Tabelas
O plano de execução que se segue é um exemplo de uma árvore típica de profundidade à esquerda, implementado com NESTED LOOP .
Observe como cada tabela é acessada por meio de índices.
O exemplo também mostra como forçar um loop aninhado usando hints ordered e use_nl.
O exemplo especifica o acesso as tabelas na mesma ordem em que aparecem na cláusula FROM.
O último especifica qual método join é usado para juntar as outras tabelas para a primeira tabela .
SELECT /*+ ordered use_nl(t2 t3 t4) */ t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 238K| 1218 (1)| 00:00:15 |
| 1 | NESTED LOOPS | | 1000 | 238K| 1218 (1)| 00:00:15 |
| 2 | NESTED LOOPS | | 100 | 18000 | 116 (0)| 00:00:02 |
| 3 | NESTED LOOPS | | 10 | 1180 | 12 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 57 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 610 | 10 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T2_T1_ID | 10 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | T3 | 10 | 620 | 11 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | T3_T2_ID | 10 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | T4 | 10 | 640 | 11 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | T4_T3_ID | 10 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=19)
7 - access("T1"."ID"="T2"."T1_ID")
9 - access("T2"."ID"="T3"."T2_ID")
11 - access("T3"."ID"="T4"."T3_ID")
O Processamento deste tipo de plano de execução pode ser resumido da seguinte forma
1.A primeira linha é obtida , não quando a consulta é analisada ou executada, o processamento começa por obter a primeira linha que atende o filtro t1.n = 19 da tabela t1.
2.Com base nos dados encontrados na tabela t1, a tabela t2 é procurada. Perceba que o Oracle aproveita a condição do join t1.id = t2.t1_id para acessar a tabela t2. Na verdade, nenhuma restrição é aplicada a essa tabela. Apenas a primeira linha que atende a condição do join é restituída à operação principal.
3.Com base nos dados encontrados na tabela t2, a tabela t3 é procurada. Também neste caso, o oracle tira proveito de uma condição de join, t2.id = t3.t2_id, para acessar a tabela T3. Apenas a primeira linha que preenche a condição de join é devolvida para a operação pai.
4.Com base nos dados encontrados na tabela t3, a tabela t4 é procurada. Aqui novamente o Oracle se aproveita de uma condição de join, t3.id = t4.t3_id, para acessar a tabela T4. A primeira linha que cumpre a condição de join é imediatamente devolvida ao cliente.
5.Quando as linhas subseqüentes são buscadas, as mesmas ações são realizadas para as buscar as seguintes.
Obviamente, o processo é reiniciado a partir da posição do ultimo resultado (que poderia ser a segunda linha que corresponde na tabela t4, se houvesse). É essencial destacar que os dados são devolvidos assim que uma linha que atende à solicitação é encontrada. Em outras palavras, não é necessária à plena execução do join antes de retornar a primeira linha.
No próximo post eu vou abordar como os MERGE JOIN são realizados.
