Brief overview:
Database is Oracle 11.
I have a C# program that reads a text file (1 value per line) and runs an Oracle query to return results, and I write the results to a file. In the WHERE clause, the program inserts a single value read from the text file, so the only thing that changes is this value from the text file as it iterates through the file. In other words, if the text file has 10,000 values, then the query is executed 10,000 times for each value.
Example:
WHERE AccountNumber = '<single value from text file>';
My question: Is it faster to run this 10,000 times or will using an "IN" operator and running it 10 times be quicker? (Since "IN" is limited to 1,000 values).
I tried to Google the answer and I found an article, but it does not quite answer my question because it only discusses about the EQUAL operator vs the IN operator for a single value.
https://stackoverflow.com/questions/37828398/performance-differences-between-equal-and-in-with-one-value
I did try to run an EXPLAIN plan, but I am by no means an expert, so I am not sure if what I am reading will be correct. I did a small sample with 4 values (1 that would return 0 rows). Essentially, the EXPLAIN plan using the EQUAL operator came back with a cost of 70 and the IN operator came back with 253. I assume that the "IN" would be faster and only require 1 trip to the DB vs the total cost of 280 (70 * 4) for each individual value and 4 trips to the DB. However, perhaps this can be a misconception of mine.
"AccountNumber" is indexed and for the sake of argument, let's just assume that the query is written in the most optimal way. I understand that there are other ways to achieve the same results, such as creating a temp table and then construct a query this way.
Thanks in advance!