Я наткнулся на запрос во время работы и не мог понять, как именно он работает. Что делает запрос, так это ищет всех родителей человека, который является его родителем сегодня.
Хитрость заключается в том, что у каждой родительско-дочерней связи есть срок, в течение которого она действительна.
Возьмите этот набор данных в качестве эталона:
GrandParent является родителем Отца с 01-01-2012 по 02-02-2015
Отец является родителем ребенка с 01.01.2012 по 02.02.2011.
Ребенок - это просто человек самого низкого уровня
NewFather является родителем ребенка с 01 января 2012 г. по 02 февраля 2014 г.
теперь список родителей, действительных на сегодняшний день для Child, должен состоять только из NewFather
чтобы получить список, ранее мы использовали этот SQL:
SELECT connect_by_root per_id2 AS per_id2,
per_id1,
LEVEL AS per_level,
n.entity_name
FROM ci_per_per pp,
ci_per_name N
WHERE N.per_id = per_id1
AND start_dt <= SYSDATE
AND ( end_dt IS NULL
OR end_dt >= SYSDATE )
START WITH per_id2 = :personID
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2;
где personID
— связанная переменная
этот запрос не работал, потому что поведение предложения where таково, что оно сначала получает все записи, а затем проверяет условия отсутствия соединения (проверки даты начала и даты окончания). Это приводит к тому, что он дает список родителей как NewFather, GrandParent
, что совершенно НЕПРАВИЛЬНО!
Таким образом, запрос был изменен на следующее:
SELECT connect_by_root per_id2 AS per_id2,
per_id1,
LEVEL AS per_level,
n.entity_name
FROM ci_per_per pp,
ci_per_name N
WHERE N.per_id = per_id1
AND start_dt <= SYSDATE
AND ( end_dt IS NULL
OR end_dt >= SYSDATE )
START WITH per_id2 = (SELECT per_id
FROM ci_acct_per
WHERE per_id = :personID
AND pp.start_dt <= SYSDATE
AND ( pp.end_dt IS NULL
OR pp.end_dt >= SYSDATE ))
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2;
Теперь то, что я не понимаю, это:
как может условие where в предложении start with влиять на поведение запроса таким образом?
Еще одна вещь, которая мне не нравится в этом запросе, заключается в том, что он использует совершенно не связанную таблицу с именем ci_acct_per
, в которой просто есть столбец per_id
для каждого человека в ci_per_per
.
Можем ли мы сделать лучше? Доступен ли более чистый подход для исправления исходного запроса?
ОБНОВЛЕНИЕ
Этот запрос работает, только если мы путешествуем выше по иерархии, а не если мы ищем детей. Однако этот запрос никогда не ищет потомков и не должен этого делать.