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
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);
Foreach $productname in products.txt: SELECT PRICE FROM PRICEMAP WHERE PRODUCT_NAME IN=$productname