этот запрос получает наборы доминирования в сети. так, например, учитывая сеть
A<----->B
B<----->C
B<----->D
C<----->E
D<----->C
D<----->E
F<----->E
это возвращается
B, E
B, F
A, E
но это не работает на большие данные, потому что я использую строковые методы в своем результате. я пытался удалить строковые методы и возвратить представление или что-то, но напрасно
With t as (select 'A' as per1, 'B' as per2 from dual union all
select 'B','C' from dual union all
select 'B','D' from dual union all
select 'C','B' from dual union all
select 'C','E' from dual union all
select 'D','C' from dual union all
select 'D','E' from dual union all
select 'E','C' from dual union all
select 'E','D' from dual union all
select 'F','E' from dual)
,t2 as (select distinct least(per1, per2) as per1, greatest(per1, per2) as per2 from t union
select distinct greatest(per1, per2) as per1, least(per1, per2) as per1 from t)
,t3 as (select per1, per2, row_number() over (partition by per1 order by per2) as rn from t2)
,people as (select per, row_number() over (order by per) rn
from (select distinct per1 as per from t union
select distinct per2 from t)
)
,comb as (select sys_connect_by_path(per,',')||',' as p
from people
connect by rn > prior rn
)
,find as (select p, per2, count(*) over (partition by p) as cnt
from (
select distinct comb.p, t3.per2
from comb, t3
where instr(comb.p, ','||t3.per1||',') > 0 or instr(comb.p, ','||t3.per2||',') > 0
)
)
,rnk as (select p, rank() over (order by length(p)) as rnk
from find
where cnt = (select count(*) from people)
order by rnk
) select distinct trim(',' from p) as p from rnk where rnk.rnk = 1`
По моему опыту, вы не хотите выполнять сложную обработку строк в больших, сложных запросах, а этот запрос довольно сложен. Я предполагаю, что эта проблема может выиграть от переосмысления и другого подхода, а не от оптимизации существующего запроса.
Как выглядят лежащие в основе таблицы и чего именно вы пытаетесь достичь? Можно ли изменить модель данных?
Одним из ограничений Oracle является то, что SQL не может обрабатывать VARCHAR2, длина которого превышает 4000 символов. Если вы попытаетесь вернуть строку, превышающую этот размер, будет выброшен ORA-01489. В идеале вы должны попытаться разбить набор результатов на несколько небольших строк. В качестве альтернативы вы можете вернуть его как CLOB.
править
как я могу вернуть вышеупомянутое как CLOB
Хмм ...
Внимательно изучив ваш код, я думаю, что единственное место, где будет брошен ORA-1489, - это эта строка:
select sys_connect_by_path(per,',')||',' as p
from people
Было бы легко обернуть этот вызов в TO_CLOB ()
. К сожалению, преобразование P в CLOB нарушает часть последующей обработки ('отличное p ,
разделение на p`), поэтому, вероятно, это не вариант. Извините.
Что касается других обходных путей ....
Есть ли у вашего сайта лицензия на Oracle Spatial? Я знаю, что не многие сайты это делают, но если ваш - один из счастливчиков (и вы используете 10gR2 или выше), вам следует ознакомиться с Oracle Spatial Network Data Model (PDF) .
В противном случае, если нет способа ограничить вывод вызова sys_connect_by_path ()
, вам, возможно, придется реализовать это в PL / SQL. Вы можете использовать ТРУБОПРОВОДНУЮ ФУНКЦИЮ , чтобы вернуть окончательный результат, чтобы вы по-прежнему могли вызывать его из оператора SELECT.