Почему CONNECT BY LEVEL на столе возвращает лишние строки?

Использование CONNECT BY LEVEL, похоже, возвращает слишком много строк при выполнении на столе. Какова логика того, что происходит?

Предполагая следующую таблицу:

create table a ( id number );

insert into a values (1);
insert into a values (2);
insert into a values (3);

Этот запрос возвращает 12 строк (SQL Fiddle).

 select id, level as lvl
   from a
connect by level <= 2
  order by id, level

Одна строка для каждой из таблиц A со значением столбца LVL составляет 1 и три для каждого в таблице A, где столбец LVL равен 2, то есть:

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

Он эквивалентен этому запросу, который возвращает те же результаты.

 select id, level as lvl
   from dual
  cross join a
connect by level <= 2
  order by id, level

Я не понимаю, почему эти запросы возвращают 12 строк или почему есть три строки, где LVL равно 2, и только один, где LVL равен 1 для каждого значения столбца ID.

Увеличение количества уровней, "подключенных" к 3 возвращает 13 строк для каждого значения ID. 1, где LVL равен 1, 3, где LVL равен 2 и 9, где LVL равно 3. Это, по-видимому, указывает на то, что возвращаемые строки представляют собой количество строк в таблице A для мощности значения LVL минус 1.

Я бы хотел, чтобы эти запросы были такими же, как и следующие: 6 строк

select id, lvl
  from ( select level  as lvl
           from dual
        connect by level  <= 2
                )
 cross join a
 order by id, lvl

Документация не особенно понятна для меня, объясняя, что должно произойти. Что происходит с этими полномочиями и почему не первые два вопроса совпадают с третьим?

Ответы

Ответ 1

В первом запросе вы подключаетесь только уровнем. Поэтому, если уровень <= 1, вы получаете каждую из записей 1 раз. Если уровень <= 2, вы получаете каждый уровень 1 раз (для уровня 1) + N раз (где N - количество записей в таблице). Это похоже на перекрестное соединение, потому что вы просто выбираете все записи из таблицы до достижения уровня, не имея других условий для ограничения результата. Для уровня <= 3 это делается снова для каждого из этих результатов.

Итак, для 3 записей:

Запись уровня 1: 3 (все имеют уровень 1)
Lvl 2: 3 записи с уровнем 1 + 3 * 3 записи с уровнем 2 = 12
Уровень 3: 3 + 3 * 3 + 3 * 3 * 3 = 39 (действительно, 13 записей каждый).
Уровень 4: начинает видеть рисунок?:)

Это не перекрестное соединение. Кросс-соединение будет возвращать только те записи, которые имеют уровень 2 в этом результате запроса, а при этом подключаются, вы получаете записи, имеющие уровень 1, а также записи, имеющие уровень 2, что приводит к 3 + 3 * 3 вместо просто 3 * 3.

Ответ 2

Если connect by используется без оператора start with и оператора prior, ограничений на присоединение дочерней строки к родительской строке не существует. И что делает Oracle в этой ситуации, он возвращает все возможные перестановки иерархии, подключая строку к каждой строке уровня выше.

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

Ответ 3

вы сравниваете яблоки с апельсинами при сравнении окончательного запроса с остальными, поскольку уровень LEVEL изолирован в 1-строчной двойной таблице.

рассмотрим этот запрос:

 select id, level as lvl
   from a
connect by level <= 2
  order by id, level

что это такое, начните с набора таблицы (выберите * From a). то для каждой строки, возвращаемой, соединяет эту строку с предыдущей строкой. поскольку вы не определили соединение в соединении, это фактически является декартовым объединением, поэтому, когда у вас есть 3 строки (1,2,3) 1, присоединяется к 2, 1- > 3, 2- > 1, 2 → 3, 3- > 1 и 3- > 2, и они также присоединяются к себе 1- > 1,2- > 2 и 3- > 3. эти соединения равны уровню 2. поэтому у нас есть 9 объединений, поэтому вы получаете 12 строк (3 оригинальные строки уровня 1 плюс декартово множество).

поэтому количество строк output = rowcount + (rowcount ^ 2)

в последнем запросе вы выделяете уровень для этого

select level  as lvl
           from dual
        connect by level  <= 2

который, естественно, возвращает 2 строки. это затем привязано к исходным 3 строкам, давая 6 строк в качестве вывода.

Ответ 4

Вы можете использовать технику ниже, чтобы преодолеть эту проблему:

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