oracle connector with db looping

[likhangy]

I have an oracle database connection where I want to get the data from using a column from another table to filter (let’s say table b). I used a DB Table Selector to pick the table from oracle, which has 460k rows in it.

I tried both Parameterized DB query Reader and DB Looping node with table b at port 1 and the oracle table at port 2 but both of them were stuck running with a message “Copying input object at port 2”.

Is the DB looping trying to copy the full data before the filtering with table b?

Please let me know if there is a way to mitigate it.

[ipazin]

Hello @likhangy,

according to this topic this is expected to be slow in certain cases:

DB Looping Node

I have just tried using the new DB Looping Node. I am finding that it suffers the same limitations as legacy approaches; namely that it cannot accept a large list for the IN statement. Therefore a loop of several (or many times) over the database is still necessary, which is of course very slow. Am I missing something? Can a large list be used and loop over the database only once?

You can always read data into KNIME (460k is rather small table) and then use Reference Row Filter node to filter it. Should be much faster.

Br,
Ivan

[likhangy]

Thank you for the suggestion, Ivan, I will do that. I do expect the table would continue to grow so I guess I will revisit this when the table is getting too big.