Соединения таблиц в предложении SELECT

Обработка зациклености данных

Пример организации зациклености в сведениях о маршрутах:

INSERT INTO way VALUES ( 'Новгород', 'Выборг', 135 );

Реакция на появление цикла (уже получается не иерархия) в этом случае отлична от имевшейся для CONNECT BY и будет

ERROR:
ORA-32044: cycle detected while executing recursive WITH query

Упражнение. Проверьте это самостоятельно.

Для предупреждения зацикливания вычислений вводится специальное указание CYCLE, где следует указать перечень (в общем случае) столбцов для распознавания хождения по кругу, придумать название столбца-индикатора (он автоматически включается в конечный ответ) и задать пару символов: для обозначения незацикленной строки и для обозначения строки, где было зафиксировано повторение значений в различительных столбцах:

WITH stepbystep ( node, route, distance ) AS (
  SELECT node, parent || '-' || node, distance
  FROM   way
  WHERE  parent = 'Москва'
     UNION ALL
  SELECT w.node
       , s.route || '-' || w.node
       , w.distance + s.distance
  FROM way w
       INNER JOIN
       stepbystep s
       ON ( s.node = w.parent )
  )
  CYCLE node SET cyclemark TO 'X' DEFAULT '-'
SELECT route, distance, cyclemark FROM stepbystep
/

Ответ:

ROUTE                                        DISTANCE C
------------------------------------------ ---------- -
Москва-Ленинград                                  696 -
Москва-Новгород                                   538 -
Москва-Новгород-Ленинград                         717 -
Москва-Ленинград-Выборг                           831 -
Москва-Новгород-Ленинград-Выборг                  852 -
Москва-Ленинград-Выборг-Новгород                  966 -
Москва-Ленинград-Выборг-Новгород-Ленинград       1145 X
Москва-Новгород-Ленинград-Выборг-Новгород         987 X

Упорядочение результата

Для придания порядка строкам результата в запросах с CONNECT BY используется собственная конструкция ORDER BY SIBLINGS. Аналогичным образом в вынесенном рекурсивном запросе применяется особое указание SEARCH. В его рамках программистом задается в том числе вымышленное имя столбца, в котором СУБД автоматически проставит числовые значения и который самостоятельно включит в порождаемый набор столбцов. На этот столбец программист может сослаться далее уже в обычной фразе ORDER BY для создания нужного порядка строк.

Пример:

ROLLBACK;
WITH stepbystep ( node, route, distance ) AS (
  SELECT node, parent || '-' || node, distance
  FROM   way
  WHERE  parent = 'Москва'
     UNION ALL
  SELECT w.node
       , s.route || '-' || w.node
       , w.distance + s.distance
  FROM way w
       INNER JOIN
       stepbystep s
       ON ( s.node = w.parent )
  )
  SEARCH DEPTH FIRST BY node DESC SET orderval
SELECT   route, distance, orderval 
FROM     stepbystep
ORDER BY orderval DESC
/

Ответ:

ROUTE                                      DISTANCE   ORDERVAL
---------------------------------------- ---------- ----------
Москва-Ленинград-Выборг                         831          5
Москва-Ленинград                                696          4
Москва-Новгород-Ленинград-Выборг                852          3
Москва-Новгород-Ленинград                       717          2
Москва-Новгород                                 538          1

Подробности и прочие свойства построений указания SEARCH приведены в документации по Oracle.

Замечание об общей формулировке запроса

Общая формулировка рекурсивного запроса в стандарте SQL и в Oracle способна вызвать у некоторых программистов недоумение, однако она имеет свое вероятное обоснование. Ранее упоминалось о возможности описания реляционной БД средствами логики предикатов. В таком случае база представляет собой набор истинных утверждений. Пусть есть "предикатный символ" (predicate symbol, то есть "обозначение утверждения") way ( x, y ) как общее обозначение однотипных утверждений (километраж и вероятные другие свойства здесь для простоты опущены как несущественные). В БД представлено несколько конкретных соответствующих ему истинных утверждений, например:

way ( 'Ленинград ', 'Выборг ' )
way ( 'Новгород ', 'Ленинград ' )
...

То есть "имеется путь от Ленинграда до Выборга", "от Новгорода до Ленинграда" и так далее. Это так называемое "существовательное" (intensional) определение БД, явно перечисляющее объекты с их свойствами. Дополнительно можно ввести еще один предикатный символ route (x, y) со смыслом "маршрут". Допустим, что утверждения для него представлены не "существовательно", а "расширительно" (extensionally), в виде двух правил вывода:

route ( x, y ) → way ( x, y )
route ( x, y ) → route ( x, z ), way ( z, y ) 

Это позволяет получать из БД сведения (о "маршрутах"), напрямую в ней не представленные, и БД становится "расширительной". Так, она оказалась дополнена группой новых утверждений вида route ( x, y ).

Теперь если обозначить way ( x, y ) как W, route ( x, y ) как R, второе (рекурсивное) правило вывода как , то с позиций уже реляционной алгебры для новых сведений из БД для определения маршрутов можно предложить формулировку ^{1 Идея получения подобной формулы упоминается в книге Марков А. С., Лисовский К. Ю. Базы данных: Введение в теорию и методологию. // М.: Финансы и Статистика, 2006, интересной разработчику и программисту БД во многих других отношениях.}. Она удивительно напоминает общее построение рекурсивного запроса в SQL, где однако пошли дальше и обобщили операцию объединения множеств на упомянутую группу. Если обобщенную множественную операцию указать как , формула будет выглядеть как .

Получается, что рекурсивная формулировка запроса в SQL придает вообще-то "существовательной" базе, предполагаемой этим языком, некоторые качества "расширительной", где возможно получение новых "знаний" из имеющихся. К сожалению, на практике такое достижение нельзя подкрепить созданием представления данных (view) на основе рекурсивного запроса ввиду имеющегося в настоящее время в Oracle запрета на подобное действие.

Оборотной стороной помимо риска зацикленности (для избежания которого Oracle дает упоминавшееся частичное решение) является пониженная производительность вычисления. Для повышения производительности Oracle тоже предлагает определенную гамму решений (организация materialized view и прочее), но все они носят неполный характер и обременены собственными издержками.