tOracleOutput Hint Options

Four Stars

tOracleOutput Hint Options

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.

 

 

Employee

Re: tOracleOutput Hint Options

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 :-)

Four Stars

Re: tOracleOutput Hint Options

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

Employee

Re: tOracleOutput Hint Options

Hi,

 

    Could you please try APPEND_VALUES hint for your case?

 

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

 

    image.png

 

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 :-)

Four Stars

Re: tOracleOutput Hint Options

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

 

 

 

 

Employee

Re: tOracleOutput Hint Options

@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 :-)

Four Stars

Re: tOracleOutput Hint Options

Hi Nikhil,

Thanks for your suggestions. 

 

Regards

Sridharan

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog