Stream: implementers
Topic: Oracle ORA-01975 when iterating over large datasets
Joe Atterberry (Feb 28 2022 at 15:55):
We are iterating over a large dataset, over a million records. As we go through the links to get the next set of resource we encounter the Oracle message ORA-01975 Maximum number of expressions in a list is 1000. Has anyone seen this and does it have anything to do with configuration settings?
Gino Canessa (Feb 28 2022 at 16:01):
There is not quite enough info here for me to be confident in my response (e.g., what FHIR server is doing the access, etc.), but if I had to guess, I would say it is likely that the paging is being done by excluding records in a SQL in clause (e.g., select ... where not in ({ids that have already been sent})).
Oracle has a limit of 1000 elements in a single in expression - you can get around it by having the code limit a single in to 1000 elements, then adding another list with an or joining them.
Again, shooting from the hip without more info though. Hope this helps!
Joe Atterberry (Feb 28 2022 at 16:45):
Hi Gino. Can that be done on the server side? HAPI generates the query so can we affect that somehow? When we go to the next set of values using something like http://localhost:1111/my-fhir/v1?_getpages=fc2a0deb-bf8e-4556-ba57-7e7dcf9d6e4c&_getpagesoffset=10&_count=20&_pretty=true&_bundletype=searchset can we change the query to add a list as you suggest? @James Agnew @Lloyd McKenzie
Gino Canessa (Feb 28 2022 at 16:55):
Hi Joe, if you are using HAPI, you may get a better response on the #hapi stream. I am not familiar enough with HAPI's code to offer more.
James Agnew (Feb 28 2022 at 17:05):
What version of HAPI FHIR are you using? If not 5.7.0, definitely start there. There are a few fixes for this kind of thing in this version.
James Agnew (Feb 28 2022 at 17:17):
..and if so, do you have a stack trace from the logs?
Joe Atterberry (Feb 28 2022 at 23:10):
Yes, thank you. We stumbled across bug https://github.com/hapifhir/hapi-fhir/issues/3123 which was fixed in 5.6.0. We're at 5.4.2 and accessing going to 5.6.2. It looks like there are a few database changes we are going to have to make. Thank you for your timely response.
Last updated: Apr 12 2022 at 19:14 UTC
