Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL DML with big in-clause - Oracle

 
Rahul Chaitanya
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to execute a SQL query having around 51000 distinct parameters in the IN clause:




Can someone advice the best way to do this? Can we just use a SQL to do this or we need a stored procedure? And how to check the performance?

The values that were present in the IN clause cannot be fetched from any other tables, it is something that needs to be taken from a flat file.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you use an IN() condition, the system needs to parse it and turn it into a pseudo table anyway. Regardless, you are likely to hit some line limit somewhere with that many entries.

If the values come from a file, the easiest method is likely to use an external table making the query a simple WHERE NOT EXISTS() query.

Alternatively, you can load the data into the database pretty quickly with the bulk loader, and run it off an internal table.

I would suggest the former unless some policy prevents you from doing so.
 
Rahul Chaitanya
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Neither of the above optios are not possible because of some policy restrictions. And I can't use any internal table, as I won't be able to modify the existing data that is being shown in the system live. Any other suggestions please?
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Those seem like utterly pointless restrictions to me.
Trying to do this without some form of temporary table you will hit the limits of either JDBC or the database around some aspect or other (Oracle for example has a limit on IN clauses that you'll easily break with this).
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rahul Chaitanya wrote:Neither of the above optios are not possible because of some policy restrictions. And I can't use any internal table, as I won't be able to modify the existing data that is being shown in the system live. Any other suggestions please?

The restriction on internal tables make no sense to me. Just because it is there does not mean it is seen. Even if it was, you could use temporary a table which will only show in your own session. Perhaps you should speak with your DBA. I have found DBAs willing to work out a solution, especially so you do not do the next solution, which in comparison is much slower and wasteful of network and processor resources.

That would be to prepare a statement and run it 51,000 times:

While we're on the subject, i would like to state my opinion that flags are almost always a bad idea. I do not know what design you have, but i feel certain that it can be improved if you remove the flag. The mere fact that important data (the 51k values) cannot be in the database makes me think the design can be improved.
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just to echo one of Brian's points.
Your DBA is your friend.
Too many dev's view them as obstacles to achieving what they want.  To be fair this can go the other way just as often, with dev's being viewed as something unworthy of access to the precious.
But...that doesn't mean you shouldn't try to befriend them.  It works wonders.
 
Rahul Chaitanya
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The restriction on internal tables make no sense to me. Just because it is there does not mean it is seen. Even if it was, you could use temporary a table which will only show in your own session. Perhaps you should speak with your DBA. I have found DBAs willing to work out a solution, especially so you do not do the next solution, which in comparison is much slower and wasteful of network and processor resources.


This is a restriction that can't be changed either by me nor you. if you know a better solution then share it otherwise, thats fine.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rahul Chaitanya wrote:
The restriction on internal tables make no sense to me. Just because it is there does not mean it is seen. Even if it was, you could use temporary a table which will only show in your own session. Perhaps you should speak with your DBA. I have found DBAs willing to work out a solution, especially so you do not do the next solution, which in comparison is much slower and wasteful of network and processor resources.

This is a restriction that can't be changed either by me nor you.

I have doubts about that statement. Have you spoken to the DBA? I have worked in large companies where the DBAs were more than happy to help, and indeed had processes in place to make it happen. All i had to do was ask. If this is a one time thing, they will likely do it for you, as this is what they are paid to do (and generally thank you for asking). If it is something that must be done multiple times, ask the DBA for advice, or to find the appropriate processes for this, or to ask for an exception. In my experience, i have found that the DBAs want you to ask, and at times will get excited about it too, or can offer advice or a better way to do it. This is their job.

Rahul Chaitanya wrote:if you know a better solution then share it otherwise, thats fine.

I have already listed a third way in the post above. While not the prettiest solution, it will work.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic