topic Re: tOracleOutput Hint Options in Talend Studio

tOracleOutput Hint Options

Anonymous — Thu, 23 May 2019 13:18:51 GMT

Hi,

We need to stop Oracle11g creating REDO logs while inserting rows. Once option suggested is to use Hint options in tOracleOutput component. The hint option ' /*+ APPEND*/ ' could not be used as the syntax requires "insert into select". Instead tried ' /*+ APPEND_VALUES*/ ' but the logs are still being created. Does anyone know how to do this?. Thanks.

Re: tOracleOutput Hint Options

Anonymous — Thu, 23 May 2019 14:37:12 GMT

Hi,

First of all, adding hints in Oracle queries is not a right method of processing SQL queries as Oracle optimizer gives better performance for queries when you are using cost based method rather than rule based method due to the advancement in its internal engines.

So if you do not need logging while creating by adding NOLOGGING at table level during table creation.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces004.htm#ADMIN11374

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: tOracleOutput Hint Options

Anonymous — Thu, 23 May 2019 15:40:58 GMT

Hi Nikhil,

Thanks. The tablespace and tables are all set to nologging on our DB, but we still have the logs being created. Any other suggestions.

Thanks

Sridharan

Re: tOracleOutput Hint Options

Anonymous — Thu, 23 May 2019 15:50:25 GMT

Hi,

Could you please try APPEND_VALUES hint for your case?

https://oracle-base.com/articles/11g/append-values-hint-11gr2

I would also recommend you to use Bulk components available for Oracle if your ultimate aim is to load the data with maximum throughput where your input data volume is high.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: tOracleOutput Hint Options

Anonymous — Mon, 27 May 2019 06:31:18 GMT

Hi Nikhil,

Currently APPEND_VALUES is what I am using, but doesn't seem to have any effect.

It is mentioned, " APPEND_VALUES hint gives us better performance by allowing us to use direct-path inserts within the FORALL statement. ".. Does it work only when FORALL statement is used? In the screenshot, how do we mention the FORALL loop?

Thanks

Sridharan

APPEND_VALUES.PNG

Re: tOracleOutput Hint Options

Anonymous — Mon, 27 May 2019 16:37:43 GMT

@Sridharan

Unfortunately I am not fully sure about the various Hints in Oracle DB. I would suggest you to get the help of an Oracle DBA also at this juncture as lot of verification need to happen from Oracle end.

Please ask them to verify whether the hints are correctly getting generated from the Oracle Admin console. We need to check the query about FORALL loop also with them as they can see the queries executed for a specific time.

http://www.dba-oracle.com/t_find_historical_sql_by_date.htm

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: tOracleOutput Hint Options

Anonymous — Tue, 28 May 2019 05:44:55 GMT

Hi Nikhil,

Thanks for your suggestions.

Regards

Sridharan