Implementing SQL Optimization Techniques

This section discusses how to implement various optimization and other coding tips into SQL generated by PeopleTools. The examples presented here only illustrate the techniques. I am not suggesting that any or all of the changes will actually improve the performance of the specific examples.

Views

Most views in PeopleSoft are defined in Application Designer in the usual way, as a block of SQL. All the standard techniques for improving a query can be brought to bear on views. Some views are defined as a query and have to be edited via the Query design tool, but they can always be changed to normal views.

Views can also be a useful way to introduce a hint via the back door. A reference to one record that is a table can be replaced in PeopleSoft with a different—but identical—record that is a view. That view may simply select the original table (in which case you can still update the table via the view), but now you can add a hint to the view. The scope of the hint is not limited to the view, but it can affect the query that references the view.