Stream join from Oracle Database

[Anonymous]

Hi All,

I would like to join multiple tables(say 10 tables) from Oracle database each having huge amount of Data. I was looking at using tMap component which does the look-up for joining of tables. But i realized that this will cause Out-of-memory issue since tMap stores the look-up data in-memory.

I have seen the "enable stream" option in tMySQLInput component but couldn't see the same in tOracleInput.

1.) Is there a way for joining multiple tables in Oracle using streams?

2.) What is the best and fastest way of joining data between multiple tables considering each table having huge data?

[vapukov]

Hi,

"enable stream" for tMySQLInput not help you with lookup table, it helps to reduce loading on database server (jdbc driver) and start faster send data from server to client without waiting of query finish, but for lookup talend will load all data first.

As analog for tOracleInput you can play with use cursor settings (fetch size) - this setting manage number of rows received from database per iteration, but this is not help with lookup tables.

the most effective way to join big tables is SQL, you can put a complicated query with many joins in tOracleInput component.

sometimes, when the number of lookups small (for example huge lookup table, but smal daily delta which must be enriched from lookups), it is possible to use a variable for filter lookup table and query for each row (https://help.talend.com/reader/hCrOzogIwKfuR3mPf~LydA/Ll_zYlcDgWNvb7kwSypEQQ )

this must resolve memory issues