ORA-01795: maximum number of expressions in a list is 1000

Question

In c# we are building a query for NHibernate containing an "in statement". The number of expressions are crossing over 5000. If I execute the query I get an error.

I need to find a good way to break the large string builder and store them in an array of string builder and execute multiple queries if needed to get the desired output. We have only one account that has over 5000 records and the rest are all below 100. Can someone suggest a way to resolve this?

score 6 · Answer 1 · edited Feb 25 '13 at 09:11

6

The solution I have used is to split the IN with OR.

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

becomes

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

This is straightforward and imposes no special requirements to the format of the query.

Seems to me that this is easier to implement in your stringbuilder (SQLQuerybuilder or whatever it's called in your case) than some other suggested solutions.

edited Feb 25 '13 at 09:11

Spontifixus

6,570
9
45
63

answered Aug 02 '11 at 10:12

lokori

436
5
6

score 3 · Answer 2 · answered Mar 10 '11 at 01:38

You can convert IN to a JOIN using this trick

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

This query is identical to

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

I know it increases the 'amount of text' in query but there isnt a performace impact as anyways both use temporary resultset without the hassle of creating global temp table.

score 2 · Answer 3 · answered May 30 '17 at 11:24

The original post question is getting old, but I have met this problem several times lately and have found a solution that might be of help to others. And, as none of the answers above has given an answer with NHibernate code, I also think this is relevant to post.

In our software product, the users make a selection in the user interface. The front end then passes a list of the corresponding ids to the back end for data querying and manipulation.

First, I needed a function to split up the list of elements into paritions of 1000 elements.

Note, this is VB.NET, but the function itself was found elsewhere on StackOverflow in C#:

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

This function I have used in several ways. One way is a loop over the list partitions to modify a QueryOver to make a Union of all the part results, like this:

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

Another way I have used this is to create an Restriction, that can be applied in QueryOvers. The following function creates such a restriction (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

I call that function like this:

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...)

I voted up: It is rather unusual to see such a great answer from a user with low reputation. Good work Nils! — Noel Widmer, May 30 '17 at 11:54

score 0 · Answer 4 · answered Mar 10 '11 at 00:49

0

You could create a temp table, populate it with the items in the IN-list, and join against it.

answered Mar 10 '11 at 00:49

Tim

5,371
3
32
41

creating stored procedures or temp tables is not an option. No access to db whatsoever. code has to be testable with nUnit and need to have CI in place. – VolleyBall Player Mar 10 '11 at 16:03

score 0 · Answer 5 · answered Mar 10 '11 at 00:54

0

You can union together the selects with 1000 of the IN in each select. Not as performant as creating a temp table, but for ad-hoc queries it works.

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
)

answered Mar 10 '11 at 00:54

Michael Shimmins

19,961
7
57
90

I will try this and let you know how it went. From the look of this, one query would be sufficient to get the desired output. – VolleyBall Player Mar 10 '11 at 16:05

score 0 · Answer 6 · edited May 23 '17 at 12:29

when you have that many items in a where clause, it might be time to consider refactoring.

you could create a stored proc that takes in a comma separated value string. the stored proc could then generate a table from that csv and you could do a inner join with your other table to produce the records.

take a look at this link to learn how to create a function that takes in a csv and returns a table so that you can query against it.

how to convert csv to table in oracle or How to best split csv strings in oracle 9i

ORA-01795: maximum number of expressions in a list is 1000

6 Answers6

Linked