Save Abstract Plan
To save the abstract plan from the SQL Optimizer window to the database
-
Open the Save Abstract Plan window by clicking
.
-
In the Save to group drop-down field, select the group where you want to store your abstract plan.
-
In the SQL drop-down field, select the SQL statement you want to associate with an abstract plan. This is usually your original SQL statement.
-
In the Abstract plan from SQL drop-down field, select the SQL statement that you determined is the best abstract plan for your application.
-
Click Save.
Note: Only save an abstract plan that is compatible with the original SQL text. Otherwise, the abstract plan will not be used the next time you execute the original SQL.
When the Abstract Plan is saved, it is only saved for the user that you are logged on as. In order for another user to use this abstract plan, you must export/import the plan to another user.
At the prompt "Plan has been created successfully. The id is nnnnnnnnnn". Click OK. The abstract plan is saved in Adaptive Server.
Note: Adaptive Server saves the abstract plan in the sysqueryplans system table. When a query is executed, Adaptive Server looks in the sysqueryplans table for a stored SQL text that matches the query. If a match is found, the saved abstract plan is used to execute the query.
Saving the abstract plan onto the database means that when the same SQL statement is executed, the query plan is based on the abstract plan.
Warning: When saving the abstract plan, Adaptive Server automatically trims the white-spaces from the SQL text replacing it with one space. You need to make sure the SQL statement you execute in your application is the same as the original SQL text that you saved with the abstract plan. If the SQL text does not match, then the abstract plan will not be used.
Here are some examples you need to be aware of:
Spaces in between functions
where substring ( EMP_NAME, 1, 5 ) = 'SMITH'
This is not the same as
where substring(EMP_NAME,1,5) = 'SMITH'
Spaces in between database, scheme and object name
from sqlexp . sqlexp . EMPLOYEE
This is not the same as
from sqlexp.sqlexp.EMPLOYEE
Parameter replacement
where EMP_ID = @var_a
This is not the same as
if @var_a = 56
where EMP_ID = 56
Comments
where EMP_ID =123 /* comment */
This is not the same as
where EMP_ID = 123