Рассчитать процент корня, принадлежащего его родителям.

Ответ 1

Это заслуживает ответа; хотя будьте осторожны, мы работаем в нескольких особых обстоятельствах.

В первую очередь следует упомянуть, что наилучшим способом сделать это является рекурсивный факторинг подзапроса/рекурсивный CTE в соответствии с Даниэлем Хильгарт и jonearles в комментариях:

with temp (id, parent_id, percent_owned, calc) as (
  select a.id, a.parent_id, a.percent_owned, percent_owned as calc
    from hierarchy_test a
   where id = 1
   union all
  select a.id, a.parent_id, a.percent_owned, a.percent_owned * t.calc as calc
    from temp t
    join hierarchy_test a
      on t.parent_id = a.id
         )
select * 
  from temp

Свой скрипт SQL..

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

Это не обязательно означает, что мы вернулись к CONNECT BY. Существует возможность рассчитать иерархии навалом. К сожалению, это оказалось невозможным; час в базе данных разбился. Трижды. Мы использовали почти 100 ГБ UNDO, и сервер просто не мог справиться.

Это особые обстоятельства; мы должны вычислить сотни тысяч иерархий за несколько часов, максимум. Среднее значение составляет около 1,5 уровней глубиной, возможно, 5-10 листьев и всего 8-12 узлов. Однако выбросы имеют 90k узлов, 27 уровней и множественные циклические отношения. Выбросы не встречаются достаточно редко.

Итак, CONNECT BY. Бенчмаркинг решение Annjawn против PL/SQL EXECUTE IMMEDIATE, предложенное в вопросе, показало, что для выше среднего дерева XMLQuery() было в 4 раза медленнее. Отлично, был ответ; нет другого варианта; оставьте это на этом.

нет.

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

Нет очевидного ответа на это, поэтому, возвращаясь слишком назад, решение Annjawn заканчивается в 3 раза быстрее! Блокировка булавки кеш-памяти полностью исчезает, и мы снова находимся на прямой и узкой.

нет.

К сожалению, похоже, что ошибка Oracle в 11.2 появляется, когда вы объединяете CONNECT BY, XMLQuery() и DBMS_SCHEDULER. В определенных обстоятельствах, обычно в больших иерархиях, он течет огромные объемы памяти. Потерял базу данных и сервер, обнаружив, что один из них. Был подготовлен отчет с Oracle, и мы тестируем его в 12c; хотя утечки памяти показывают меньше, они все еще появляются, так что 12c не работает.

Решение? Оберните XMLQuery() функцией PL/SQL. Утечка памяти была решена, к сожалению, это вызвало большой резонанс в этой функции, и мы начали получать многочасовой кеш библиотеки: mutex x ждет. Запрос x$kglob подтвердил, что он был XMLTYPE, который забивался.

Андрей Николаев рекомендует изменять систему; скорее не делайте этого, когда все остальное работает нормально, или используя процедуру DBMS_POOL.MARKHOT, чтобы сообщить Oracle, что вы будете получать доступ к этому объекту много, Для случайного взгляда это, возможно, решило проблему, однако, примерно через 10 минут, и, пройдя то, что, казалось, было каждой блокировкой, имеющейся у Oracle, мы закончили с 5 процессами, соперничающими за процессор. По-видимому, не было достаточно (54 ГБ и 24 ядра на тестовой коробке)...

Затем мы начали получать курсорный вывод: s ждет. Берлсон рекомендует более скрытый параметр finangling, Джонатан Льюис предлагает что он уменьшился до SGA. Поскольку БД использовала автоматическую калибровку SGA, мы старались постепенно увеличивать общий пул, до 30 ГБ и только возвращали старого друга в кеш библиотеки: mutex x wait.

Итак, какое решение? Кто знает, это честный ответ, но хранимая процедура Java работает блестяще до сих пор, утечек памяти, нет ожиданий и значительно быстрее, чем все остальное.

Я уверен, что там больше... и мне очень хотелось бы заставить предложение MODEL работать, если у кого есть какие-то идеи?

P.S. Я не могу претендовать на все это; это работа около 3 человек, чтобы довести нас до этой стадии...

Ответ 2

В 11g, возможно, что-то вроде -

SELECT a.*, LEVEL AS lvl
      ,XMLQuery( substr( sys_connect_by_path( percent_owned, '*' ), 2 ) RETURNING CONTENT).getnumberval() AS calc
   FROM hierarchy_test a
  START WITH id = 1
CONNECT BY nocycle PRIOR parent_id = id;

SQL Fiddle.

Или, как ваш трюк '1'|| -

SELECT a.*, LEVEL AS lvl
      , XMLQuery( ('1'|| sys_connect_by_path( percent_owned, '*' )) RETURNING CONTENT).getnumberval() AS calc
   FROM hierarchy_test a
  START WITH id = 1
CONNECT BY nocycle PRIOR parent_id = id;

К сожалению, в 10g XMLQuery не может принимать функции и всегда ожидает строковый литерал для оценки, например -

select XMLQuery('1*0.25' RETURNING CONTENT).getnumberval() as val 
  from dual;

работает и возвращает 0.25, но

select XMLQuery(substr('*1*0.25',2) RETURNING CONTENT).getnumberval() as val
   from dual;

дает ORA-19102: XQuery string literal expected.

Запрос может замедляться по мере увеличения количества уровней на дереве с дополнительными накладными расходами на создание внутреннего дерева с помощью XMLQuery. Наиболее оптимальным способом достижения результата будет PL/SQL-функция, которая, кстати, будет работать как в 10g, так и в 11g.