Syntax

REOPTIMIZE [[EXPLAIN] | [COMMIT]] [<reoptimize_level>]

Remarks

• The REOPTIMIZE command is used with the PROFILE command to look for improved query plans.

• REOPTIMIZE works on the most recently profiled query in the current connection.

• The keyword EXPLAIN shows the explain output for the reoptimized plan.

• Subsequent REOPTIMIZE statements will generate differing plans (<reoptimize_level>). Refer to specific subsequent plans with the corresponding number of the order in which the REOPTIMIZE was executed. For example, if you run REOPTIMIZE three times consecutively on the same query profile, you can rerun the second of the optimizations by referring to its place in the order of execution, like so:

  SQL

  Copy

  REOPTIMIZE 2;

• To save a reoptimize plan to apply to a query, use the COMMIT keyword:

  SQL

  Copy

  REOPTIMIZE COMMIT 2;

  After running the above statement, the query will use the plan generated the second time REOPTIMIZE was executed.

• Refer to the Permission Matrix for the required permission.

Example

PROFILE <query text>;
REOPTIMIZE EXPLAIN; -- prints the explain of the reoptimized plan
REOPTIMIZE; -- runs the reoptimized plan as a profile;
SHOW PROFILE; -- shows the reoptimized profile
REOPTIMIZE; -- Runs reoptimize with the previous two executions stats
REOPTIMIZE; -- Runs reoptimize with the previous three executions stats
REOPTIMIZE 2; -- Runs the reoptimize plan from the second reoptimize
REOPTIMIZE COMMIT 2; -- Runs and saves the second reoptimize plan to <query text>
<query text>; -- Uses the committed reoptimize plan