As an Oracle DBA administering a PeopleSoft database, we would like to ensure that all PeopleSoft queries (PRIVATE or PUBLIC) are optimized. There is always a possibility of introducing a HINT to tune the SQL generated by PeopleSoft in Query Manager. Here are the steps to add the HINT.
1. Create a new expression
2. Click“Add Field” and select the first field that is used in the query
7. Now, you have a query ready with your chosen HINT
Scenario with DISTINCT
If you have chosen DISTINCT in the query properties then your SQL will appear as shown below.
Oracle will ignore the HINT in this case because the DISTINCT precedes the HINT. To correct this behavior follow the below steps.
1. From the query properties uncheck “DISTINCT”