Иерархические запросы: сравнительный анализ в Oracle и PostgreSQL

Большинство современных СУБД (систем управления базами данных) являются реляционными, т. е. представляют данные в виде двух— многомерная таблица, в которой имеются строки (записи) и столбцы (поля записей). Но на практике мы часто сталкиваемся с другой организацией данных, а именно с иерархической.

Иерархические запросы являются важным инструментом в области управления данными, позволяющим эффективно работать с иерархическими структурами данных, такими как организационные деревья, деревья категорий, генеалогические структуры и т. д. Эти запросы играют ключевую роль в выявлении связей между данными и извлечение информации в виде древовидных структур.

Проблема в том, что данные с иерархической структурой очень плохо представлены в реляционной модели. В стандарте SQL-92 нет средств для их обработки.

Но такие инструменты появились в стандарте SQL-1999. Однако к тому времени у Oracle уже был собственный оператор CONNECT BY. Несмотря на это, в SQL 1999 синтаксис рекурсивных запросов полностью отличается от синтаксиса CONNECT BY в Oracle и использует ключевое слово WITH. Реализация рекурсивных запросов в других СУБД несколько запоздала, поэтому она появилась в MS SQL Server только в версии 2005.

Помимо синтаксиса, существуют различия и в терминологии. В Oracle часто обсуждаемые запросы называются «иерархическими», а все остальные — «рекурсивными». Суть этого не меняется; Я буду использовать оба.

В этой статье мы остановимся на рассмотрении иерархических запросов в двух популярных системах управления базами данных — Oracle и PostgreSQL. Обе платформы обладают уникальными функциями и подходами к обработке иерархических данных, и наше знакомство с ними позволит читателю лучше понять контекст их приложения.

Иерархическую структуру можно представить как набор узлов, где каждый узел связан с одним или несколькими узлами более высокого уровня. Важные термины включают родителей, детей, листья и корни.

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

Реализация в Oracle

Для демонстрации мы будем использовать тестовую таблицу сотрудников, состоящую из 3 полей:

id – идентификатор,

имя — имя сотрудника,

manager_id — идентификатор менеджера (относится к идентификатору другой записи в той же таблице)

create table test_employees (
    id number primary key,  
    name varchar2(32),
    manager_id number
);

Заполним таблицу тестовыми данными по следующему дереву:

insert into test_employees(id, name, manager_id)
values (1, 'Henry Anderson', null);

insert into test_employees(id, name, manager_id)
values (2, 'Julia Miller', 1);

insert into test_employees(id, name, manager_id)
values (3, 'Mark White', 1);

insert into test_employees(id, name, manager_id)
values (4, 'Paul Scott', 2);

insert into test_employees(id, name, manager_id)
values (5, 'Daniel Hill', 2);

insert into test_employees(id, name, manager_id)
values (6, 'Maria Lopez', 3);

insert into test_employees(id, name, manager_id)
values (7, 'Monika Moore', 3);

insert into test_employees(id, name, manager_id)
values (8, 'Alex Smith', 4);

insert into test_employees(id, name, manager_id)
values (9, 'Brandon Brown', 4);

insert into test_employees(id, name, manager_id)
values (10, 'Elizabeth Jackson', 5);

insert into test_employees(id, name, manager_id)
values (11, 'Kevin Green', 6);

insert into test_employees(id, name, manager_id)
values (12, 'Jessica Johnson', 7);

insert into test_employees(id, name, manager_id)
values (13, 'Lisa Williams', 7);

insert into test_employees(id, name, manager_id)
values (14, 'Angela Martinez', 7);

В Oracle иерархические запросы появились в версии 8, задолго до появления стандарта. Поэтому по-прежнему используется совершенно другой синтаксис.

Необязательный оператор START With сообщает Oracle, где начать цикл, т. е. какая строка (или строки) будет корневой. Условие может быть практически любым, вы даже можете использовать функции или внутренние запросы: manager_id имеет значение null, или manager_id = 1, или даже name, например 'Henry%' .

Оператор CONNECT BY является обязательным. Он устанавливает отношения между родительскими и дочерними элементами иерархии. В условии оператора CONNECT BY абсолютно необходимо использовать унарный оператор PRIOR, который ссылается на предыдущую запись.

Как это работает? Oracle находит первую запись, удовлетворяющую условию в START With, и начинает поиск следующей. В то же время доступ к этой первой записи можно получить через PRIOR. Если мы все сделали правильно, то Oracle будет искать записи, в которых поле для хранения информации о родителе (manager_id) будет содержать значение, равное id нашей первой записи.

Таким образом, будут найдены все потомки корневой записи. А поскольку процесс рекурсивный, аналогичный поиск будет продолжаться с каждой найденной строкой, пока не будут найдены все потомки. Также возможно использование псевдостолбца rownum, в котором строки нумеруются начиная с 1 в порядке их выдачи. И еще нам очень пригодится псевдостолбец LEVEL, показывающий уровень в иерархии. Итак, 1-я запись будет иметь уровень 1, ее потомки - уровень 2, потомки потомков - 3 и т.д.

select level, te.id, te.manager_id, te.name 
from test_employees te
start with te.manager_id is null
connect by prior te.id = te.manager_id
order siblings by te.id

Используя ключевое слово siblings, мы говорим, что вам нужно сортировать только в пределах одного уровня иерархии. Это станет понятнее, если убрать все ненужные поля в запросе и добавить поля:

select lpad('     ', 3 * level) || te.name as Tree
from test_employees te
start with te.manager_id is null
connect by prior te.id = te.manager_id
order siblings by te.id

TREE
   Henry Anderson
      Julia Miller
         Paul Scott
            Alex Smith
            Brandon Brown
         Daniel Hill
            Elizabeth Jackson
      Mark White
         Maria Lopez
            Kevin Green
         Monika Moore
            Jessica Johnson
            Lisa Williams
            Angela Martinez

NOCYCLE — параметр для исключения циклических ссылок. Используйте этот параметр вместе с псевдостолбцом CONNECT_BY_ISCYCLE, чтобы увидеть, какие строки содержат цикл.

Реализация в PostgreSQL

Мы используем одну и ту же тестовую таблицу.

create table test_employees (
    id integer primary key, 
    name text,
    manager_id integer
);

Запросы на заполнение таблицы данными будут выглядеть точно так же, как в Oracle.

with recursive employees(id, manager_id, name) as (
    select te.id, te.manager_id, te.name
    from test_employees te
    where te.manager_id is null

    union all

    select te.id, te.manager_id, te.name
    from employees e, test_employees te
    where  te.manager_id = e.id
    )
select id, manager_id, name from employees e;

Вычисление рекурсивного запроса:

1. Рассчитывается нерекурсивная часть. Для UNION (но не UNION ALL) повторяющиеся строки отбрасываются. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную рабочую таблицу. Нерекурсивная часть является аналогом START With для запроса Oracle.
<ли>

Пока рабочий лист не пуст, следующие шаги повторяются:

<ли>

Рекурсивная часть рассчитывается так, что рекурсивная ссылка на сам запрос ссылается на текущее содержимое рабочего листа. Для UNION (но не UNION ALL) отбрасываются повторяющиеся строки и строки, дублирующие ранее полученные. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную промежуточную таблицу.

2. Содержимое рабочей таблицы заменяется содержимым промежуточной таблицы, а затем промежуточная таблица очищается.

В чем еще отличия от Oracle? Удобств типа уровня нет, но их можно сделать самостоятельно:

with recursive employees(id, level, manager_id, name) as (
    select te.id, 1, te.manager_id, te.name
    from test_employees te
    where te.manager_id is null

    union all

    select te.id, e.level + 1, te.manager_id, te.name
    from employees e, test_employees te
    where  te.manager_id = e.id
    )
select id, level, manager_id, name from employees e;

Рекомендации по выбору между Oracle и PostgreSQL для иерархических данных

Решение Oracle и PostgreSQL об обработке иерархических данных зависит от по ряду факторов.

• Структура данных. Если структура данных представляет собой сложную иерархию с большим количеством уровней вложенности, Oracle может обеспечить более эффективное выполнение запросов благодаря оптимизированной реализации.
• Масштаб проекта. Для крупных предприятий с высокими требованиями к производительности и поддержке Oracle может предоставить полный набор инструментов и функций для обработки сложных иерархий.
• Гибкость и расширяемость. PostgreSQL с открытым исходным кодом может быть более гибким решением для проектов, которым необходима свобода адаптации к изменениям в иерархических данных.

В целом возможности PostgreSQL примерно такие же, как и в Oracle, хотя, на мой взгляд, он немного менее удобен и прозрачен.

---

Оригинал