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

3. Add your HINT before the field name in the expression

4. Click Ok
5. Select the Expression as a field in the query and move it as the first column
6. Deselect the first field A.SETID that has now moved to be the second column


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”

2. Edit the expression holding the HINT and add DISTINCT as shown below.

3. Now the query SQL will appear corrected.


Posted by Nitin Pai
Comments (1)
August 17th, 2007

Comments (1)

lan li - January 28th, 2009

Hi I'd like to ask expert about " hint in peoplesoft". Except can choose "first_rows", "all_rows", can the sql be added the hint using index? Example, /*+ index (tb_name index_name )*/ Thanks,