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.

  1. Use SQL loader to load data in a table then query using where in or a join
  2. 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:


I've to query db as

Option 1:

Run a sqlloader on file products.txt and load contents into TMPTABLE.

Option 2:

Foreach $productname in products.txt:
  • When do you manage your Oracle Text index? – Michael Kutz Feb 11 '20 at 19:45
  • Generally when importing data you want to try to get it into the database as quickly and as simply as possible and then do your data transformations there, as that's what databases are good at. I guess that's your Option 1. – Colin 't Hart Feb 12 '20 at 20:41

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)

  • Edited question, added more details. I do not have to store the data, I only have to use this data to query the table. Will insertion still be beneficial. – Anubhav Feb 12 '20 at 6:14
  • The first query is what I would go with. If storage isn't an issue, I would just import the data into a table. This would also let you create an index which could help speed up and reduce overhead. If storage is limited and you know you'll need to run this query multiple times, I would create a stored procedure to make life easier. Either way it will save you time down the road if you need to use the data again. – lFreightTrain Feb 12 '20 at 14:11

Option 1 is definitely much better than option 2. Option 2 will do 1mio single row accesses to the PRICEMAP table, whereas option 1 will be implemented as a plain join: the query optimizer will flatten your query into a join. Depending on the size of the tables, that will be either a nested loop (with TMPTABLE as the driving table), or hash join (likely since you match both tables entirely).

There is actually an option 3: it is like option 1, except that the file is defined as an external table. Meaning that will use the same query, but TMPTABLE really points to your text file. It would save you the time to load the data into the database - although that time is likely to be minor. I'd say a couple of minutes at most on modern hardware.

Read about external tables here: https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-external-tables.html

External tables are a very powerful mechanism. They can read data from a variety of sources, including also HDFS or HIVE.

Not the answer you're looking for? Browse other questions tagged or ask your own question.