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

Question

In C #, we create a query for NHibernate containing "in statement". The number of expressions crosses more than 5000. If I execute the request, I get an error message.

I need to find a good way to break up a large string builder and store them in an array of string builders and execute a few queries if necessary to get the desired result. We only have one account with more than 5000 entries, and all the others are below 100. Can anyone suggest a way to solve this problem?

Answer 1

The solution I used is to split IN into OR .

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

becomes

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

It is simple and does not require special requirements for the request format.

It seems to me that this is easier to implement in your row builder (SQLQuerybuilder or something else in your case) than some other suggested solutions.

Answer 2

You can convert IN to 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 request is identical

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

I know that it increases the amount of text in the query, but does not affect performance, since in any case, using temporary result sets without the hassle of creating a global temp table.

Answer 3

The original question by mail is getting old, but I have encountered this problem several times recently and found a solution that could help others. And, since none of the answers above gave an answer with NHibernate code, I also think that this is relevant to the post.

In our software product, users make choices in the user interface. Then the front end passes the list of matching identifiers to the back end for queries and data manipulation.

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

Note that 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 

I used this function in several ways. One way is to loop over sections of the list to modify QueryOver to create a union of all results, for example:

 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 used is to create a Constraint 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 this function as follows:

 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

You can create a temporary table, populate it with IN-list elements and join it.

Answer 5

You can combine the selection with 1000 of IN in each selection. It is not so important how to create a temporary table, but for special 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 ) 

Answer 6

when you have many elements in the where clause, maybe it's time to consider refactoring.

you can create a stored proc that takes a string of values ​​separated by a comma. the stored proc can then generate a table from this csv, and you can make an internal join with your other table to create the records.

take a look at this link to find out how to create a function that accepts in csv and returns a table so you can query it.

how to convert csv to table in oracle or what is the best way to split csv rows in oracle 9i