Efficient approach to Query DB with data in text file I've got an oracle DB set up and want to Query it after I receive data in a text file. Number of product-names present in text file are around a million.

After Searching a lot I've found 2 ways of doing same but I'm not sure which'll be more efficient. 


Use SQL loader to load data in a table then query using where in or a join
Query DB for each product name in loop.


The second option is a consideration just because I think insert(using loader)+select will be more expensive than only selects.

P.S.: I would prefer less load on DB server rather than less time.

File contents are something like:

ABC
BCD
FGS
LTB
.
.
NHL


I've to query db as

Option 1:

Run a sqlloader on file products.txt and load contents into TMPTABLE.
SELECT PRICE FROM PRICEMAP WHERE PRODUCT_NAME IN (SELECT PRODUCT_NAME FROM TMPTABLE);


Option 2:

Foreach $productname in products.txt:
    SELECT PRICE FROM PRICEMAP WHERE PRODUCT_NAME IN=$productname
Every server is different, but you might be surprised how quickly you can query through a million rows. Are you inserting the data into a table, or are you just trying to query against it?

If you're just trying to query against the file but don't need to store the data in the database, you could insert it into a Temp Table and run your queries against it.

If you're inserting the file into a table I would go with your first option. One query is going to cause less load on the server than running 1 million queries (if I understood you correctly). Without knowing your query I can't help with performance/load. It may not be applicable, but a UNION is typically faster than a JOIN. Just something else to keep in mind.

(Sorry for the answer and not a comment, I don't have the rep yet)