ORA-01795 Due to Autogenerated IN Statements That Are Too Long When Connecting to an Oracle DB

[TomHodkinson]

I'm working on an application where sequelize has been used to connect to an oracle DB and pull back some data. Unfortunately for one of the larger export queries with many joins and nested data I've had to set the separate setting to true on several nested joins in order to prevent the data call from taking several minutes to come back with data and often end up timing out before the results can be built. With that setting set though, the performance is actually really good, even larger exports come back with data in a matter of seconds instead of minutes!

The problem is that under the hood it seems sequelize uses IN statements to get this data, but this becomes a major problem when the number of things in that IN statement exceeds the built in limit of 1000 items in oracle as the export then fails and I've been looking all afternoon and it doesn't seem like there is any obvious way to tell sequelize to just break those up into multiple IN statements or better yet use tuples to vastly increase the limit. It's looking more and more likely that I'll have to majorly rewrite the query unless there is a way to tell sequelize to build its IN statements differently.

Any ideas for how to approach fixing an issue like this without a more major rewrite of the query?

[TomHodkinson]

Alright, I did end up figuring out a nice path forward on this and since I haven't seen any issues or other discussion referencing this bug/limitation (I'm not sure if there is a good reason longer IN lists aren't handled automatically so there may yet be very good reasons for that or if not it may just be a nice to have future enhancement!), I just wanted to describe what my solution to this issue ended up looking like as it didn't end up being that big a rewrite and by running sub pieces of the query in parallel I was able to get pretty good performance for a query that really needs a materialized view in the long run (assuming you have the DB connections/resources to support running those queries in parallel)

The trick is to run the outer query that fetches the list of IDs that are used in the IN statement generated by sequelize when one of the included models is run with the separate option set to true. With the results of that query I call ".get({ plain: true })" on each record to get the data as plain JSON and from there I can build a unique list of IDs then split them up into and array of arrays where each array has a max of 1000 items (as that is the default oracle limit on IN statements). I then call .map on that array of arrays and return a promise that runs the query for each set of 1000 items and adds them to a shared dictionary of values (using the ID as a key) and then I call Promise.all on the list of promises returned by the map call. This lets me await them all in parallel and once all the calls have come back with data I add that data to the original result set from the first query at the right part of the hierarchy by ID and return the object.

Honestly the most finicky part was getting the async await to always await (for example, don't use a forEach when awaiting each set of 1000 IDs as each loop fails to return a promise and so nothing ends up getting awaited, but a map works great and lets you do the promise all trick I mentioned above!), but with that figured out the performance is better than it has any right to be! Sure this still needs a refactor or MV to handle continued growth of data, but this works great for now and hopefully this helps someone else out if they get stuck here as I surprisingly could not find much else on this kind of problem when searching!