Иерархический запрос в Oracle

← →
Андрей Жук © (2005-07-11 16:13) [0]

То есть есть у нас таблица фактов, например из полей
id, some_analit_id, some_value, где some_analit_id - внешний ключ из таблицы
Analit1, имеющей стандартную древообразную структуру.
Можно ли в Oracle получить одним запросом (не процедурой) дерево, в котором
данные будут "подняты на верхние уровни"?

← →
evvcom © (2005-07-11 18:07) [1]

А теперь на пальцах, плиз. Пример таблиц и что должно выйти.

← →
Reindeer Moss Eater © (2005-07-11 20:57) [2]

Посторить дерево по таблице Analit1 - можно.
Связать с ней таблицу, имеющую на нее FK - тоже можно.

← →
Petr V. Abramov © (2005-07-12 01:16) [3]

В Oracle все можно. :)
Давайте поймем, что конкретно нужно.

← →
Андрей Жук © (2005-07-13 14:31) [4]

> Посторить дерево по таблице Analit1 - можно.

кто бы спорил

> Связать с ней таблицу, имеющую на нее FK - тоже можно.

это тоже можно.
Пример.
Есть иерархия
Фрукты Апельсины Апельсин испанский Апельсин турецкий Бананы Банан западноафриканский Банан бразильский Яблоки Симиренко Лимонка
Есть данные о продажах.
Апельсин испанский - 10$, Апельсин турецкий - 7$, Банан западноафриканский - 13$, Банан бразильский - 5$, Симиренко - 3$, Лимонка - 2$.
Запрос должен возвратить
Фрукты 30 $ Апельсины 17$ Апельсин испанский 10$ Апельсин турецкий 7$ Бананы 8$ Банан западноафриканский 5$ Банан бразильский 3$ Яблоки 5$ Симиренко 3$ Лимонка 2$

← →
Petr V. Abramov © (2005-07-13 15:42) [5]

select q.FRUCT_NAME, s.AMOUNT from ( select rownum RNUM, f.FRUCT_ID, f.NAME FRUCT_NAME, level LEVEL_NUM from FRUCT f start with PARENT_ID is null connect by prior FRUCT_ID = PARENT_ID ) q, SALES s where s.FRUCT_ID = q.FRUCT_ID order by q.RNUM

← →
ANB © (2005-07-13 15:48) [6]

> Petr V. Abramov © (13.07.05 15:42) [5]
- сей запрос вернет только самый нижний уровень. Верхний будет совсем обрезан, и суммы по группам, ессно, не будет.

← →
Petr V. Abramov © (2005-07-13 15:50) [7]

Лажанулся...

← →
Petr V. Abramov © (2005-07-13 15:53) [8]

Тем не менее идея верная. Соединение с подзапросом сделать внешним, суммы аналитическими ф-циями посчитать можно.

← →
Reindeer Moss Eater © (2005-07-13 16:18) [9]

Само дерево
create table tree_table(
id number,
pid number,
name varchar2
);

Значения для дерева
create table tree_values(
id number,
fk number,
somevalue number
);

select t.*,
(select sum(somevalue) from tree_values where fk in(select id from tree_table tt connect by prior tt.id = tt.pid start with tt.id = t.id))
from tree_table t
connect by prior t.id = t.pid
start with t.id = 1

← →
evvcom © (2005-07-13 16:27) [10]

Чтобы собрать суммы, надо использовать select ... cube ... Для использования cube надо, чтобы можно было записи сгруппировать, например, по родителю. Родителя можно вычислить аналитической функцией LAG, где сортировать по rownum. Но rownum можно накладывать только после order siblings by, чтобы получить нужное дерево, причем здесь аналитическая функция ROW_NUMBER уже не прокатит. Т.е. получится трехэтажный подзапрос в запросе. На скору руку накидать, значит точно будут ошибки, а отлаживать в лом. Так что дерзай, что не будет выходить, пиши.

← →
Reindeer Moss Eater © (2005-07-13 16:34) [11]

Так что дерзай, что не будет выходить, пиши.

Сто лет там никакой cube не нужен.

← →
Андрей Жук © (2005-07-13 16:42) [12]

2 Reindeer Moss Eater
Не, не катит. Очень неоптимальный запрос. На несбалансированном дереве с иерархией из около 500 записей с уровнями вложений до 6 и таблицей фактов всего в 20000 записей работает уже 5 мин. Попробуем cube

← →
Reindeer Moss Eater © (2005-07-13 16:47) [13]

Про оптимальность никто ничего не говорил.
- Можно?
- Можно!
- Как?
- Так!

Не, не катит. Очень неоптимальный запрос.
Начнем с того, что поставлена дурная задача.
И при этом ожидается оптимальное и дешевое решение.

К чему, например, требование "Одним запросом"?

← →
Андрей Жук © (2005-07-13 16:52) [14]

да как не одним запросом - это очевидно. В Firebird, например, я реализовал такое через процедуру, в Oracle сейчас это реализовано через временную таблицу.

А я бы вообще задался вопросом:
зачем мне нужен требуемый результат в виде датасета?
Да еще и в гриде?

Может дешевле сформировать результат на сервере в виде xml или просто в clob.
Отдать его на клиента и там визуализировать.

> Сто лет там никакой cube не нужен

Ладно, согласен, работает. Но проверил только на небольшой таблице. Было подозрение на [12]. И план выполнения очень интересный получился. Одно уточнение: последнюю строку лучше записать
start with t.pid is null
это на случай, если корневая группа не только "Фрукты", а есть еще и "Овощи".

С CUBE должно быть быстрее, там всего один запрос с connect by, но вложенность 6 уровней, хотя для 500 записей...

> Андрей Жук © (13.07.05 16:42) [12]
- индексы для него надо подложить. На таких объемах должен летать. Проблемы начнутся, когда за пару сотен тысяч записей перевалишь. Обязательно должны быть индексы в дереве по ID и Parent_ID, желательно наличие FK Parent_ID -> ID (легче план будет строить).

есть там индексы

Да нет, руки не дошли. С пользователями общаюсь.

Иерархический запрос в Oracle Найти похожие ветки