Очень часто за основу архитектуры приложения берётся дерево. Простой пример: есть страны, в странах — области, в областях — города, в городах — организации, в организациях — работники, товары или что-либо ещё. Использование дерева вполне логично и оправдано. Иерархичность такой системы показывает некая абстрактная таблица. Назовём её object:
Наполним её какими-нибудь данными:
При этом мы можем легко одним запросом получать нужные нам связи:
Однако проблемы появляются, когда записей в таблице становится на столько много, что любой рекурсивный запрос выполняется минуты две, а то и больше. Менять всю архитектуру как-то поздновато… Тут-то нам на помощь и приходит денормализация дерева. В этой статье я расскажу об одном из способов такой денормализации.
Основная идея заключается в использовании материализованного представления, которое хранит связи в более удобном для запросов виде:
Теперь мы имеем денормализованную таблицу, которую можем использовать в своих запросах.
Как можно увидеть, в таблице есть связи каждого объекта со всеми его родителями, а nesting_level — это число уровней до родителя. Обратите внимание, я добавил поле id, которое не обязательно, но вполне разумно, если вы планируете получать доступ к данным через ORM. Теперь вышеупомянутые запросы будут выглядеть так:
Ну и, по желанию, можно добавить индексы:
Вот и всё. От себя скажу, что на нашем проекте этот способ дал прирост скорости запросов примерно в 60 раз. Используйте с умом и не забывайте, что полученные данные будут не всегда актуальными. Рекомендую применять этот способ только к редко добавляющимся и удаляющимся объектам. Ну или тогда стоит реализовать оперативное обновление материализованного представления. Нет предела полёту фантазии…
update: Статья была подправлена и способ существенно упрощен благодаря комментарию xtender.
CREATE TABLE object (
id NUMBER(11),
parent_id NUMBER(11),
type VARCHAR2(16) NOT NULL,
name VARCHAR2(255) NOT NULL,
CONSTRAINT pk_object PRIMARY KEY (id),
CONSTRAINT fk_object_parent FOREIGN KEY (parent_id) REFERENCES object (id) ON DELETE CASCADE ENABLE
);
Наполним её какими-нибудь данными:
id | parent_id | type | name
------------------------------------------------------
1 | NULL | country | Россия
2 | 1 | region | Московская область
3 | 1 | region | Новосибирская область
4 | 2 | city | Москва
5 | 3 | city | Новосибирск
При этом мы можем легко одним запросом получать нужные нам связи:
-- Выбрать все города России
SELECT *
FROM object
WHERE type = 'city'
START WITH id = 1 CONNECT BY PRIOR id = parent_id;
-- Выбрать страну, в которой находится Новосибирск
SELECT *
FROM object
WHERE type = 'country'
START WITH id = 5 CONNECT BY PRIOR parent_id = id;
Однако проблемы появляются, когда записей в таблице становится на столько много, что любой рекурсивный запрос выполняется минуты две, а то и больше. Менять всю архитектуру как-то поздновато… Тут-то нам на помощь и приходит денормализация дерева. В этой статье я расскажу об одном из способов такой денормализации.
Основная идея заключается в использовании материализованного представления, которое хранит связи в более удобном для запросов виде:
CREATE MATERIALIZED VIEW object_fast
REFRESH COMPLETE ON DEMAND
START WITH trunc(sysdate)+4/24 NEXT (trunc(sysdate)+1)+4/24 AS
SELECT rownum id, tree.* FROM (
SELECT
CONNECT_BY_ROOT id object_id,
CONNECT_BY_ROOT name object_name,
CONNECT_BY_ROOT type object_type,
id parent_id,
name parent_name,
type parent_type,
level-1 nesting_level
FROM object
CONNECT BY PRIOR parent_id = id
ORDER BY object_id, nesting_level
) tree;;
ALTER TABLE object_fast ADD CONSTRAINT pk_object_fast PRIMARY KEY (id);
Теперь мы имеем денормализованную таблицу, которую можем использовать в своих запросах.
id | object_id | object_name | object_type | parent_id | parent_name | parent_type | nesting_level
----------------------------------------------------------------------------------------------------------------------
1 | 1 | Россия | country | 1 | Россия | country | 0
2 | 2 | Московская область | region | 2 | Московская область | region | 0
3 | 2 | Московская область | region | 1 | Россия | country | 1
4 | 3 | Новосибирская область | region | 3 | Новосибирская область | region | 0
5 | 3 | Новосибирская область | region | 1 | Россия | country | 1
6 | 4 | Москва | city | 4 | Москва | city | 0
7 | 4 | Москва | city | 2 | Московская область | region | 1
8 | 4 | Москва | city | 1 | Россия | country | 2
9 | 5 | Новосибирск | city | 5 | Новосибирск | city | 0
10 | 5 | Новосибирск | city | 3 | Новосибирская область | region | 1
11 | 5 | Новосибирск | city | 1 | Россия | country | 2
Как можно увидеть, в таблице есть связи каждого объекта со всеми его родителями, а nesting_level — это число уровней до родителя. Обратите внимание, я добавил поле id, которое не обязательно, но вполне разумно, если вы планируете получать доступ к данным через ORM. Теперь вышеупомянутые запросы будут выглядеть так:
-- Выбрать все города России
SELECT *
FROM object_fast
WHERE parent_id = 1 AND object_type = 'city';
-- Выбрать страну, в которой находится Новосибирск
SELECT *
FROM object_fast
WHERE object_id = 5 AND parent_type = 'country';
Ну и, по желанию, можно добавить индексы:
CREATE INDEX object_fast_obj_id ON object_fast (object_id);
CREATE INDEX object_fast_par_id ON object_fast (parent_id);
CREATE INDEX object_fast_obj_type ON object_fast (object_type);
CREATE INDEX object_fast_par_type ON object_fast (parent_type);
CREATE INDEX object_fast_nesting ON object_fast (nesting_level);
Вот и всё. От себя скажу, что на нашем проекте этот способ дал прирост скорости запросов примерно в 60 раз. Используйте с умом и не забывайте, что полученные данные будут не всегда актуальными. Рекомендую применять этот способ только к редко добавляющимся и удаляющимся объектам. Ну или тогда стоит реализовать оперативное обновление материализованного представления. Нет предела полёту фантазии…
update: Статья была подправлена и способ существенно упрощен благодаря комментарию xtender.