Using the WITH REOPTIMIZATION Option

Use the WITH REOPTIMIZATION keywords to reoptimize your query plan. When you prepare SELECT, EXECUTE FUNCTION, or EXECUTE PROCEDURE statements, the database server uses a query plan to optimize the query. If you later modify the data associated with the prepared statement, you can compromise the effectiveness of the query plan for that statement. In other words, if you change the data, you might deoptimize your query. To ensure optimization of your query, you can prepare the statement again, or open the cursor again using the WITH REOPTIMIZATION option.

You should generally use the WITH REOPTIMIZATION option, because it provides the following advantages over preparing a statement again:

• Rebuilds only the query plan, rather than the entire statement
• Uses fewer resources
• Reduces overhead
• Requires less time

The WITH REOPTIMIZATION option forces the database server to optimize the query-design plan before it processes the OPEN cursor statement.

The following example uses the WITH REOPTIMIZATION keywords:

EXEC SQL open selcurs using descriptor sdp with reoptimization;