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

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

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

Hint2_2
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

Hint3

7. Now, you have a query ready with your chosen HINT

Hint4

Scenario with DISTINCT
If you have chosen DISTINCT in the query properties then your SQL will appear as shown below.

Hint5

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”

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

Hint7
3. Now the query SQL will appear corrected.

Hint8

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,