ORA-01795: 列表中表达式的最大数量为 1000

Question

在 C# 中，我们正在构建一个包含“in 语句”的 NHibernate 查询。表达式的数量超过 5000。如果我执行查询，则会出现错误。

我需要找到一种好方法来打破大型字符串生成器并将它们存储在字符串生成器数组中，并在需要时执行多个查询以获得所需的输出。我们只有一个帐户拥有超过 5000 条记录，其余的都低于 100 条。有人可以建议一种方法来解决此问题吗？

Answer 1

我使用的解决方案是将 IN 与 OR 分开。

where A in (a,b,c,d,e,f)

这很

where (A in (a,b,c) OR a in (d,e,f)) ...

简单，对查询的格式没有特殊要求。

在我看来，这比其他建议的解决方案更容易在您的字符串生成器（SQLQuerybuilder 或您的情况下称为任何名称）中实现。

Answer 2

您可以使用此技巧将 IN 转换为 JOIN

SELECT * FROM maintable
JOIN (
    SELECT v1 a FROM DUAL UNION ALL
    SELECT v2 a FROM DUAL UNION ALL
    SELECT v3 a FROM DUAL UNION ALL
    ...
    SELECT v2000 a FROM DUAL) tmp

    on tmp.a = maintable.id

该查询与

SELECT * FROM maintable 
WHERE id IN (v1, v2, v3, ...., v2000)

我知道它增加了查询中的“文本量”相同，但不会对性能产生影响，因为无论如何都使用临时结果集，而无需创建全局临时表的麻烦。

Answer 3

最初的帖子问题已经过时了，但我最近多次遇到这个问题，并找到了可能对其他人有帮助的解决方案。而且，由于上面的答案都没有给出 NHibernate 代码的答案，我也认为这与帖子相关。

在我们的软件产品中，用户在用户界面中进行选择。然后前端将对应的 id 列表传递给后端进行数据查询和操作。

首先，我需要一个函数将元素列表拆分为 1000 个元素的分区。

请注意，这是 VB.NET，但该函数本身是在 C# 中的 StackOverflow 上的其他地方找到的：

Public Shared Iterator Function Partition(Of T)(source As IList(Of T), Optional size As Int32 = 1000) As IEnumerable(Of List(Of T))
        For i As Integer = 0 To CInt(Math.Ceiling(source.Count / CDbl(size)))
            Yield New List(Of T)(source.Skip(size * i).Take(size))
        Next
End Function

我以多种方式使用了该函数。一种方法是对列表分区进行循环，以修改 QueryOver 来对所有部分结果进行并集，如下所示：

Dim allPartitions As IEnumerable(Of List(Of Integer)) = Partition(idList, SplitSize)
        Dim foundObjects As IEnumerable(Of MyEntity) = New List(Of MyEntity)

        For Each part As List(Of Integer) In allPartitions
            foundObjects = foundObjects.Union(
                _session.QueryOver(Of MyEntity) _
                     WhereRestrictionOn(Function(x) x.ID).IsIn(part).Future())
        Next

我使用此方法的另一种方法是创建一个可以在 QueryOver 中应用的限制。以下函数创建了这样的限制（ICriterion）：

Public Shared Function GetRestrictionOnIds(ids As List(Of Integer), propertyName As String) As ICriterion

        Dim allParts As IEnumerable(Of List(Of Integer)) = Partition(ids, SplitSize)

        Dim restriction As Disjunction = Restrictions.Disjunction()
        For Each part As List(Of Integer) In allParts
            restriction.Add(Restrictions.In(propertyName, part))
        Next

        Return Restrictions.Conjunction().Add(restriction)

    End Function

我这样调用该函数：

Dim wellIdRestriction As ICriterion = GetRestrictionOnIds(wellIdsList, "WellId") 
Dim bleedOffs As IList(Of BleedOff) = _session.QueryOver(Of BleedOff)() _
                .Where(wellIdRestriction) _
                .Where(..... more restrictions...) _
                .And(...yet more restrictions....) _
                .List().GroupBy(...some function...) _
                .ToDictionary(.. key-function, value-function...)

Answer 4

您可以创建一个临时表，用 IN 列表中的项目填充它，然后加入它。

Answer 5

您可以将每个选择中包含 1000 个 IN 的选择合并在一起。性能不如创建临时表，但对于临时查询它可以工作。

SELECT A, B, C from BLAH WHERE A IS IN (
    0 - 999
)

UNION

SELECT A, B, C from BLAH WHERE A IS IN (
    1000 - 1999
)

Answer 6

当 where 子句中有这么多项目时，可能是时候考虑重构了。

您可以创建一个接受逗号分隔值字符串的存储过程。然后，存储过程可以从该 csv 生成一个表，您可以与其他表进行内部联接以生成记录。

查看此链接，了解如何创建一个函数，该函数接受 csv 并返回一个表，以便您可以对其进行查询。

如何将 csv 转换为 Oracle 中的表
或者
如何在 Oracle 9i 中以最佳方式拆分 csv 字符串