PeopleSoft Learning

Most of the databases has have SQL statement caching for performance improvement of the SQL execution.

This means that every time they receive SQL, they compare it against their cache of previously executed statements to see if they have seen it before. If so, they can reuse the old query plan. This works only if the SQL text matches exactly. This is unlikely with literals instead of bind variables. I,e SQL match will not consider standard bind parameters during the matching process.
Advantage of this is Database need not complie & create execution plan for every exectution and just re-use the existing one.


When to use it
1) When we AE program has Do Select/Do while / Do unitl looping construncts and you need to run the same SQL step mulitple times. It can be used even the same sql statments are present in different steps as well.
2) Use ReUse only if you do not use Bind variables for column names.
3) Use ReUse only if you have no %BINDs for Field names , Record names & logic condition.
if you have Bind for the column,Record name which will not change across multiple execution of the sql. i,e Bind value for the column , record name is same to make use of the ReUse
UPDATE PS_JOB
SET COMPRATE =
WHERE %BIND(COMP_RATECD, STATIC) = %BIND(COMPRATE)
COMPRATE value can change for every execution.
4) To re-prepare a ReUsed statement from scratch, because one of the static binds has
changed and the SQL has to reflect that, use %ClearCursor.

-Ganesh