Add User-Defined SQL

Add User-Defined SQL Alternatives

Once you have provided the original SQL statement in the SQL Optimizer window, you can create your own tweaked version of the statement and add it as a SQL alternative. You can do this either before or after you have rewritten the original SQL statement. You can then test run the user-defined SQL alternatives with the alternatives created by the SQL Rewrite and Generate Indexes functions. Or, you can simply test your alternative against the original SQL statement.

To insert your own SQL alternative

1. In the Run Time Pane on the SQL tab in the SQL Optimizer window, select a SQL alternative (or the original SQL statement) to use as a template.

2. Click .

3. In the SQL Text Pane, edit the SQL syntax to create your own version.

4. Click to retrieve the access plan for your SQL alternative. The plan is automatically parsed to determine whether its cost is identical to the cost of another alternative or the original SQL. If identical costs are found, SQL Optimizer displays a message.

Note: User-defined SQL alternatives are not checked to determine whether they are semantically equivalent to the original SQL. However, after you perform a Batch Run on the alternative, you can check the Records Returned and Remarks columns in Run Time pane to see whether the alternative's record count matches the record count for the original SQL.

To delete a user-defined SQL alternative

1. Select the alternative in the Run Time pane.

2. Select SQL | Delete User-Defined SQL.