tOracleOutput Hint Options

[Anonymous]

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.

[Anonymous]

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 🙂

[Anonymous]

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

[Anonymous]

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 🙂

[Anonymous]

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

[Anonymous]

@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 🙂

[Anonymous]

Hi Nikhil,

Thanks for your suggestions. 

Regards

Sridharan