Enter the Original SQL Statement

Enter or Edit the Original SQL Statement

You can optimize a single SELECT, DELETE, UPDATE, and INSERT SQL statement in the SQL Optimizer window. This optimization process includes running two major functions—SQL Rewrite and Generate Indexes—on this original statement to obtain alternatives on which you can test performance.

The following procedures describe how to obtain or edit this original SQL statement.

To enter the original SQL statement

1. Click .

2. Enter the SQL statement in the SQL Text pane on the SQL tab:

   • Type or paste the statement from a another location.

   • Open an existing file.

   • Send an SQL statement directly from another window using the Send SQL to the SQL Rewrite Function or Send SQL to the Generate Indexes Function function.

     Note: When you use either of these functions, the SQL Rewrite or Generate Indexes function automatically runs on the SQL once the SQL Optimizer window opens.

   • From Toad, open a SQL Optimizer session on the SQL in the Toad Editor or the SQL used in view, trigger, or MQ table DDL.

3. Perform any of these functions:

   Retrieve Access Plan

   Retrieve Run Time

   Retrieve Run Result

   Automatically Rewrite the Original SQL Statement

   Generate Index-Set Alternatives

   Retrieve the Run Time for a Group of Alternatives

To edit the original SQL statement

1. Select <Edit SQL> in the Batch Run pane.
2. Edit the SQL text in the SQL Text pane.

Notes:

• SQL Optimizer processes only a single SELECT, DELETE, UPDATE, or INSERT SQL statement.
• You can have only one SQL Optimizer window open at any one time. SQL Optimizer prompts you to save the original SQL statement if you attempt to open another file or create a new one.
• If the SQL statement uses temp tables, the temp tables must already exist before you can perform any function on the SQL statement. Create the temp tables by using the User-Defined Temp Table window.
• You can insert variables and comments within the original SQL statement.

Tip: To help construct a SQL statement, use the Editor functions: member lookup, argument lookup, auto correction, indent, outdent, comment, and uncomment functions.