Sybase WorkSpace allows you to select a SQL statement in the SQL editor and use the context menu to visually display the execution plan of the statement. This allows developers or database administrators to optimize the database and SQL code. SQL developers or database administrators can tell from the query plan when, for example, to create table indexes, reorganize data tables, or change the way a query is written.
In this lesson, you will learn how to:
Display a query plan
Change query plan preferences
Save and load query plans to and from files
View and delete previously displayed plans
This lesson does not teach you how to use a query plan,
only how to access a query plan in Sybase WorkSpace. For specific
information on how to use a query plan, see the Adaptive
Server Anywhere SQL User’s Guide.
Verify that the SybStoreDB.sql script
still has a connection to the database by looking at the Sybase
WorkSpace main window status bar. The status should include the
information “...database: MySybStore, connected”.
If the script no longer has an active connection, repeat “Lesson 1: Opening and executing a SQL script”.
In SQL editor, highlight lines 13 through 16 in the script SybStoreDB.sql:
select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='WAREHOUSE_INVENTORY_PK' and t.table_name='WAREHOUSE_INVENTORY'
Right-click in the editor and select Get Execution Plan on the context menu.
When the SQL Execution Plan view displays, click the Maximize icon.
The execution plan expands in the Sybase WorkSpace window.
When you select different nodes in the left pane, the associated information for the selected node displays in the right pane.
Now you will modify some preferences to change the behavior of the execution plan.
Click the Menu icon (small white down arrow) on the SQL Execution Plan toolbar and select Preferences from the menu.
For this lesson, you will change the plan’s executed orientation view to vertical, and the plan options to a graphics plan with statistics.
When the Preferences dialog box opens, select Query Plan in the left pane, then change these options:
SQL Execution Plan View Orientation – select Vertical Orientation.
Adaptive Server Anywhere_9.x – select this tab and under Plan Options, select Graphics Plan with Statistics from the drop-down list.
Click OK.
Close the SQL Execution Plan window by clicking the “X” icon on the title tab.
Right-click in the SQL editor window and select Get Execution Plan from the context menu to regenerate the query plan.
After you make preference changes, reselect Get
Execution Plan to see the results of those changes.
Maximize the SQL Execution Plan window to see your preference changes displayed.
Optionally, change other preferences and reselect Get Execution Plan to view how other preferences can be modified.
To save the plan for later viewing, select the SQL Execution Plan tab and click the Save Plans to a File icon on the window’s toolbar. A dialog box displays where you specify the location and filename for the plan you want to save.
To reload a plan, click the Load Plans from a File icon. A dialog box opens where you specify the path to the file from which to load a plan.
To remove (delete) the current plan, click the single “X” icon on the toolbar. To delete all plans, click the double “XX” icon.
Close the SQL Execution Plan window by clicking the “X” icon on the title tab, then select File|Close from the Sybase WorkSpace main menu to close the SQL editor. You have completed this tutorial and now know how to view and manage SQL query plans with Sybase WorkSpace.
