Подключиться по запросу
Я храню иерархические данные в таблице. Когда к ресурсу обращаются по его иерархическому пути (grantParent/parent/resource), мне нужно найти ресурс с помощью запроса CONNECT BY.
Примечание. Команды SQL экспортируются из EnterpriseDB, но они также должны работать в Oracle.
Структура таблицы:
CREATE TABLE resource_hierarchy
(
resource_id character varying(100) NOT NULL,
resource_type integer NOT NULL,
resource_name character varying(100),
parent_id character varying(100)
)
WITH (
OIDS=FALSE
);
Данные:
INSERT INTO "resource_hierarchy" (resource_id,resource_type,resource_name,parent_id) VALUES ('36d27991', 3, 'areaName', 'a616f392');
INSERT INTO "resource_hierarchy" (resource_id,resource_type,resource_name,parent_id) VALUES ('a616f392', 3, 'townName', 'fcc1ebb7');
INSERT INTO "resource_hierarchy" (resource_id,resource_type,resource_name,parent_id) VALUES ('fcc1ebb7', 2, 'stateName', '8369cc88');
INSERT INTO "resource_hierarchy" (resource_id,resource_type,resource_name,parent_id) VALUES ('8369cc88', 5, 'countryName', null);
Теперь, когда я получаю путь, как
countryName/stateName/townName/areaName
Я выполняю запрос, как,
select LEVEL,* from resource_hierarchy
WHERE resource_name = (
CASE LEVEL
WHEN 1 THEN 'areaName'
WHEN 2 THEN 'townName'
WHEN 3 THEN 'stateName'
WHEN 4 THEN 'countryName'
ELSE ''
END
)
connect by prior parent_id = resource_id
start with resource_name = 'areaName';
Мои ожидаемые результаты:
LEVEL resource_id resource_type resource_name parent_id
-------------------------------------------------------------
1 36d27991 3 areaName a616f392
2 a616f392 3 townName fcc1ebb7
3 fcc1ebb7 2 stateName 8369cc88
4 8369cc88 5 countryName <null>
Этот запрос работает нормально, но я не уверен, что он будет работать быстрее, когда моя таблица велика, как сотни тысяч записей.
Можете ли вы оптимизировать этот запрос для моего требования?
Отредактировано:
ОБЪЯСНИТЕ для вышеупомянутого запроса: я определил два индекса - один для resource_id (первичный ключ) и другой для parent_id
Sort (cost=66.85..66.86 rows=1 width=694)
Sort Key: connectby_cte.siblingssortcol
CTE prior
-> Recursive Union (cost=0.00..65.83 rows=31 width=151)
-> WindowAgg (cost=0.00..3.12 rows=1 width=83)
-> Seq Scan on resource_hierarchy (cost=0.00..3.11 rows=1 width=83)
Filter: ((resource_name)::text = 'areaName'::text)
-> WindowAgg (cost=0.33..6.21 rows=3 width=151)
-> Hash Join (cost=0.33..6.15 rows=3 width=151)
Hash Cond: ((resource_hierarchy_1.resource_id)::text = (prior.parent_id)::text)
Join Filter: connectby_cyclecheck(prior.recursionpath, (resource_hierarchy_1.parent_id)::text)
-> Seq Scan on resource_hierarchy resource_hierarchy_1 (cost=0.00..2.89 rows=89 width=83)
-> Hash (cost=0.20..0.20 rows=10 width=286)
-> WorkTable Scan on prior (cost=0.00..0.20 rows=10 width=286)
-> CTE Scan on prior connectby_cte (cost=0.00..1.01 rows=1 width=694)
Filter: ((resource_name)::text = CASE level WHEN 1 THEN 'areaName'::text WHEN 2 THEN 'townName'::text WHEN 3 THEN 'stateName'::text WHEN 4 THEN 'countryName'::text ELSE ''::text END)
3 ответа
Отказ от ответственности: Мой основной опыт относится к СУБД Oracle, поэтому обратите внимание на детали при применении решения к Postgres.
Where Предложение применяется после того, как полная иерархия уже построена, поэтому в исходном запросе ядро базы данных начало извлекать данные с указанным resource_name на любом уровне и построение полного дерева для каждой найденной записи. Фильтрация происходит только на следующем шаге.
Документация:
Oracle выбирает корневую строку (и) иерархии - те строки, которые удовлетворяют условию START WITH.
Oracle выбирает дочерние строки каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию условия CONNECT BY по отношению к одной из корневых строк.
Oracle выбирает последовательные поколения дочерних строк. Oracle сначала выбирает дочерние элементы строк, возвращаемых на шаге 2, а затем дочерние элементы этих дочерних элементов и т. Д. Oracle всегда выбирает дочерние элементы, оценивая условие CONNECT BY по отношению к текущей родительской строке.
Если запрос содержит предложение WHERE без объединения, Oracle удаляет из иерархии все строки, которые не удовлетворяют условию предложения WHERE. Oracle оценивает это условие для каждой строки отдельно, а не удаляет все дочерние элементы строки, которая не удовлетворяет условию.
Для оптимизации этой ситуации запрос должен быть изменен следующим образом (иерархия обращена к более естественному нисходящему порядку):
select
level, rh.*
from
resource_hierarchy rh
start with
(resource_name = 'countryName')
and
(parent_id is null) -- roots only
connect by
prior resource_id = parent_id
and
-- at each step get only required records
resource_name = (
case level
when 1 then 'countryName'
when 2 then 'stateName'
when 3 then 'townName'
when 4 then 'areaName'
else null
end
)
Тот же запрос может быть написан на основе синтаксиса CTE ( рекурсивный факторинг подзапросов Oracle).
Ниже приведен вариант для PostgreSQL CTE, исправленный в соответствии с предложением @Karthik_Murugan:
with RECURSIVE hierarchy_query(lvl, resource_id) as (
select
1 lvl,
rh.resource_id resource_id
from
resource_hierarchy rh
where
(resource_name = 'countryName') and (parent_id is null)
union all
select
hq.lvl+1 lvl,
rh.resource_id resource_id
from
hierarchy_query hq,
resource_hierarchy rh
where
rh.parent_id = hq.resource_id
and
-- at each step get only required records
resource_name = (
case (hq.lvl + 1)
when 2 then 'stateName'
when 3 then 'townName'
when 4 then 'areaName'
else null
end
)
)
select
hq.lvl, rh.*
from
hierarchy_query hq,
resource_hierarchy rh
where
rh.resource_id = hq.resource_id
order by
hq.lvl
Это только половина работы, потому что мы должны помочь ядру базы данных находить записи, создавая соответствующие индексы.
Запрос выше содержит два поисковых действия:
1. Найдите записи для начала;
2. Выберите записи на каждом следующем уровне.
Для первого действия нам нужно проиндексировать resource_name поле и, возможно, parent_id поле.
Для полей второго действия parent_id а также resource_name должен быть проиндексирован.
create index X_RESOURCE_HIERARCHY_ROOT on RESOURCE_HIERARCHY (resource_name);
create index X_RESOURCE_HIERARCHY_TREE on RESOURCE_HIERARCHY (parent_id, resource_name);
Возможно создание только X_RESOURCE_HIERARCHY_TREE Индекса достаточно. Это зависит от характеристик данных, хранящихся в таблице.
PS Строка для каждого уровня может быть построена из полного пути с помощью substr а также instr функции как в этом примере для Oracle:
with prm as (
select
'/countryName/stateName/townName/areaName/' location_path
from dual
)
select
substr(location_path,
instr(location_path,'/',1,level)+1,
instr(location_path,'/',1,level+1)-instr(location_path,'/',1,level)-1
)
from prm connect by level < 7
Немного другой запрос к тому, что придумал @ThinkJet. Это работает в ЕАБР и дает ожидаемые результаты.
WITH RECURSIVE rh (resource_id, resource_name, parent_id, level) AS
(
SELECT resource_id, resource_name, parent_id, 1 as level FROM resource_hierarchy
where resource_name = 'countryName' AND parent_id IS NULL
UNION ALL
SELECT cur.resource_id, cur.resource_name, cur.parent_id, level+1 FROM resource_hierarchy cur, rh prev WHERE cur.parent_id = prev.resource_id AND
cur.resource_name = (
CASE level
WHEN 3 THEN 'areaName'
WHEN 2 THEN 'townName'
WHEN 1 THEN 'stateName'
END
)
)
SELECT * FROM rh
Редактировать: этот запрос может совпадать даже с частичными совпадениями, но мы всегда можем убедиться, что количество записей = количество элементов URL. Также, если URL содержит только один элемент (например, /countryName), удалите часть UNION из вышеприведенного запроса, чтобы получить ожидаемый результат.
select
LEVEL,
resource_id,
resource_type,
resource_name,
parent_id
from
resource_hierarchy
connect by prior parent_id = resource_id
start with UPPER(resource_name)= UPPER(:resource_name);
Используя этот подход, вам не придется использовать операторы CASE. Просто упоминание имени ресурса приведет к получению родительских иерархий.