SQL 我怎样才能摆脱这个查询中的“ORA-01489：字符串连接的结果太长”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2533150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i get rid of 'ORA-01489: result of string concatenation is too long' in this query?
提问by core_pro
this query gets the dominating sets in a network. so for example given a network
此查询获取网络中的支配集。所以例如给定一个网络
A<----->B
B<----->C
B<----->D
C<----->E
D<----->C
D<----->E
F<----->E
A<----->B
B<----->C
B<----->D
C<----->E
D<----->C
D<----->E
F<----->E
it returns
B,E
B,F
A,E
but it doesn't work for large data because i'm using string methods in my result. i have been trying to remove the string methods and return a view or something but to no avail
它返回
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`
回答by APC
One of Oracle's limits is that SQL cannot handle VARCHAR2 bigger than 4000 characters. If you attempt to return a string exceeding this size it hurls ORA-01489. Ideally you should try to break down the resultset into multiple small rows. Alternatively you could return it as a CLOB.
Oracle 的限制之一是 SQL 无法处理大于 4000 个字符的 VARCHAR2。如果您尝试返回超过此大小的字符串，则会抛出 ORA-01489。理想情况下，您应该尝试将结果集分解为多个小行。或者，您可以将其作为 CLOB 返回。
edit
编辑
how i can return the above as a CLOB
我如何将上述内容作为 CLOB 返回
Hmm...
唔...
Having looked closely at your code I think the only place which is going to hurl ORA-1489 is this line:
仔细查看您的代码后，我认为唯一会抛出 ORA-1489 的地方是这一行：
select sys_connect_by_path(per,',')||',' as p
from people
It would be easy to wrap that call in TO_CLOB(). Unfortunately turning P into a CLOB breaks some of the subsequent processing ('distinct p,partition by p`) so it probably isn't an option. Sorry.
将该调用包装在TO_CLOB(). 不幸的是，将 P 转换为 CLOB 会破坏一些后续处理（'distinct p ,partition by p`），因此它可能不是一种选择。对不起。
As for other workarounds....
至于其他解决方法......
Does your site have a license for Oracle Spatial? I know not many sites do, but if yours is one of the lucky ones (and you're using 10gR2 or higher) then you should check out Oracle Spatial Network Data Model (PDF).
您的站点是否拥有 Oracle Spatial 的许可证？我知道没有多少网站这样做，但如果您是幸运的网站之一（并且您使用的是 10gR2 或更高版本），那么您应该查看Oracle Spatial Network Data Model (PDF)。
Otherwise, if there is no way to restrict the output of the sys_connect_by_path()call, you might just have to implement this in PL/SQL. You can use a PIPELINED FUNCTIONto return the final output so you can still call it from a SELECT statement.
否则，如果没有办法限制sys_connect_by_path()调用的输出，您可能只需要在 PL/SQL 中实现它。您可以使用PIPELINED FUNCTION返回最终输出，以便您仍然可以从 SELECT 语句中调用它。
回答by Chris
In my experience you do not want to do complex string handling in large, complicated queries, and this query is quite complicated. I would guess that this problem very well could benefit from a rethink and a different approach rather than an optimization of the existing query.
根据我的经验，您不想在大型、复杂的查询中进行复杂的字符串处理，而且这个查询非常复杂。我猜想这个问题很可能受益于重新思考和不同的方法，而不是对现有查询的优化。
What does the underlying tables look like and what exactly are you trying to achieve? Is it possible to alter the data model?
底层表是什么样的，你到底想实现什么？是否可以更改数据模型？
