How to make PSQuery issue literal values for prompt values
Last updated on JANUARY 10, 2017
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.44 to 8.54 [Release 8.4]
Information in this document applies to any platform.
SQL queries generated by PS Queries are typically issued with bind variable as place holders for the prompted values. This technique allows the optimizer to reuse an existing plan in an effort to reduce compile time. Often the same PS Query is used to select vastly different sized data using different prompt values, e.g. different date ranges. When this occurs, the plan compiled initially may perform poorly for the subsequent prompt values.
This document explains a way to make PSQuery issue literal values for the prompted values. This technique is valuable in order to force the database optimizer to recompile the execution plan and thus avoid problems related to the use of bind variables that results in a single plan being used on a wide range of data volumes.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms