Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement. A bit about Oracle Query hints: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E

This article explains the reasons why certain Oracle query hints may not be pushed down to the Oracle Database .

This article explains why, in some cases, Oracle query hints may not get pushed down to the underlying Oracle database.

All supported environments

1) By default, Oracle hints do get pushed to the database when the functions are pushed down to the Database , However, in scenarios where the function is not pushed to the Database , the Oracle hint disappears from the FETCH SQL. Refer to the below screenshot:



Here, /*+use_hash*/ is the Oracle query hint and TZCONVERTOR is the problematic function. It is because the function itself does not get pushed down to the Oracle data source which is observed by the "No Push Reason" given below:



2) After making the necessary changes in the oracle_<version>_thin_driver_values.xml file, the user should be able to see the function getting pushed down to the data source.

 3) Now that the function is pushed down, the Oracle hints get pushed down as well, as seen in the below screenshot:



In the provided screenshot, it's noticeable that only the 'SELECT' and 'FETCH' nodes are visible, while the 'FUNCTION' node has disappeared. This indicates a successful push-down of the function along with the query hint.

NOTE: For Step 2, which involves pushing down the Oracle function, kindly reach out to the TIBCO support team and the team will assist you through the process.