0

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:

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
Improve this question
2
  • When do you manage your Oracle Text index? Commented Feb 11, 2020 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. Commented Feb 12, 2020 at 20:41

2 Answers 2

Reset to default
1

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)

Improve this answer
2
  • 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. Commented Feb 12, 2020 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. Commented Feb 12, 2020 at 14:11
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.