Porque é que o CONNECT por nível numa tabela devolve linhas extra?
usar o CONNECT por nível parece devolver demasiadas linhas quando executadas numa tabela. Qual é a lógica por trás do que está acontecendo?
assumindo o seguinte quadro:
create table a ( id number );
insert into a values (1);
insert into a values (2);
insert into a values (3);
esta pesquisa devolve 12 linhas (SQL Fiddle).
select id, level as lvl
from a
connect by level <= 2
order by id, level
uma linha para cada uma no quadro A, sendo o valor da coluna LVL 1 e três para cada uma no quadro A, sendo a coluna LVL 2, ou seja:
ID | LVL ---+----- 1 | 1 1 | 2 1 | 2 1 | 2 2 | 1 2 | 2 2 | 2 2 | 2 3 | 1 3 | 2 3 | 2 3 | 2
é equivalente a esta consulta, que retorna os mesmos resultados.
select id, level as lvl
from dual
cross join a
connect by level <= 2
order by id, level
não entendo por que essas consultas retornam 12 linhas ou por que existem três linhas onde o LVL é 2 e apenas uma onde o LVL é 1 para cada valor da coluna ID.
O aumento do número de níveis que estão "ligados" a 3 devolve 13 linhas para cada valor de ID. 1 em que a LVL é de 1, 3 em que a LVL é de 2 e 9 em que a LVL é de 3. Isto parece sugerir que as linhas devolvidas são o número de linhas na tabela a para a potência do valor de LVL menos 1.
Eu teria pensado que estes as consultas seriam as mesmas que as seguintes, que retornam 6 linhasselect id, lvl
from ( select level as lvl
from dual
connect by level <= 2
)
cross join a
order by id, lvl
4 answers
Na primeira consulta, você se conecta apenas pelo nível. Então, se Nível Então para 3 registos:
- Lvl 1: 3 registo (todos com nível 1)
- registos Lvl 2: 3 com nível 1 + 3*3 registos de Nível 2 = 12
- Lvl 3: 3 + 3*3 + 3*3*3 = 39 (na verdade, 13 registros cada).
- Lvl 4: a começar a ver um padrão? :)
Quando connect by é usado sem a cláusula start with e prior operador, não há restrições em juntar a linha de filhos a uma linha de origem. E o que a Oracle faz nesta situação, ela retorna todas as possíveis permutações de hierarquia, conectando uma linha a cada linha de nível mais alto.
SQL> select b
2 , level as lvl
3 , sys_connect_by_path(b, '->') as ph
4 from a
5 connect by level <= 2
6 ;
B LVL PH
---------- ----------
1 1 ->1
1 2 ->1->1
2 2 ->1->2
3 2 ->1->3
2 1 ->2
1 2 ->2->1
2 2 ->2->2
3 2 ->2->3
3 1 ->3
1 2 ->3->1
2 2 ->3->2
3 2 ->3->3
12 rows selected
Você está comparando maçãs com laranjas ao comparar a consulta final com as outras, como o nível é isolado na tabela dual de 1 linha.
Vamos considerar esta consulta:
select id, level as lvl
from a
connect by level <= 2
order by id, level
O que isso quer dizer é, Comece com o conjunto da tabela (selecione * de a). então, para cada linha retornada conecte esta linha à linha anterior. como você não definiu uma junção na conexão por, isso é de fato uma junção cartesiana, então quando você tem 3 linhas de (1,2,3) 1 se junta a 2, 1->3, 2->1, 2->3, 3->1 e 3- > 2 e associam - se também a si próprios 1->1,2->2 e 3->3. Estas juntas são de Nível=2. então temos 9 juntas lá, e é por isso que você tem 12 linhas (3 linhas originais "nível 1" Mais o conjunto cartesiano).
De modo que o número de linhas de saída = número de linhas + (número de linhas^2)
Na última consulta você está isolando o nível para este
select level as lvl
from dual
connect by level <= 2
O qual, obviamente, devolve 2 linhas. este é então cartesimado para as 3 linhas originais, dando 6 linhas como saída.
Pode usar a técnica abaixo para ultrapassar este problema:
select id, level as lvl
from a
left outer join (select level l from dual connect by level <= 2) lev on 1 = 1
order by id