SQL Optimizer identifies columns to use as index alternatives for a SQL statement after it analyzes SQL syntax, relationships between tables, and selectivity of data. SQL Optimizer then combines identified alternatives into index sets.
To generate an index alternative
Select the Optimize SQL tab in the main menu.
Enter a SQL statement in the Alternative Details pane.
Click . The Select Connection and Schema window displays.
Select a connection and schema to use.
Select Index Details in the SQL Information pane to view index generation information.
To test an index, select it in the Alternatives pane and click .
Note: The Test Run function allows you to test an index set SQL Optimizer generated. It physically creates the indexes on the database, runs the SQL statement, retrieves execution statistics, and drops the indexes. Since this process physically creates indexes on your database, it may impact performance of other SQL statements.
The Deploy Outline function in Optimize SQL improves SQL statement performance without changing your original source code. Using Optimize SQL, you can generate SQL statements that are semantically equivalent to your original SQL statement with alternative execution plans. Once you identify the best alternative for your database environment, you can deploy it as a stored outline to use with your original statement.
To deploy an outline
Click the arrow beside and select New SQL Rewrite Session.
Enter your original SQL statement in the Alternative Details pane and click . The Select Connection and Schema window displays.
Right-click the alternative you want to deploy as an outline in the Alternatives pane and select Deploy Outline. The Deploy Outline window displays.
Review the following for additional information:
Outline name | Enter a name for the stored outline |
Category |
Click and select a previously created category or enter a new category name. Notes:
|
Note: You can use the Outline Management feature in Manage Plans to enable and disable categories or to move outlines to different categories.
You can use Best Practices to analyze your SQL statement and database to recommend common techniques for improving database performance. Since the recommendations can also affect performance of other statements in your database, you should review and test the recommendations before implementing them. When evaluating the recommendations, take into account that database performance is affected by the following:
System resources (CPU, I/O, memory, database architecture, and more)
Data distribution
System architecture
SQL execution plans
User's usage behavior
Note: The Best Practices function is only available in SQL Rewrite mode in Optimize SQL.
To view best practices
Click .
Tip: To display the best practices tab, click , select Optimize SQL | Best Practices | General, and select the Display Best Practices tab in SQL Rewrite mode checkbox.
Enter a SQL statement into the Alternative Details pane.
Click . The Select Connection and Schema window displays.
Review the recommendations provided.
You can analyze the impact of new indexes on your SQL statement's execution plans before you physically create the indexes on your database. You can create indexes in Optimize SQL or Optimize Indexes and then send the index or indexes to Analyze Impact to evaluate the impact on your SQL workload.
Use the Optimize SQL module to generate index alternatives for a single SQL statement. Or use the Optimize Indexes module to generate index alternatives for a SQL workload or group of SQL statements.
To send indexes to Analyze Impact from Optimize SQL
Generate index alternatives using the Index Generation feature in Optimize SQL.
Select the virtual index alternative you want to use for the analysis in the Alternatives pane in Optimize SQL.
Click in Optimize SQL. A new Analyze Impact session opens.
The Indexing Change Details page is populated with the new index that you sent from the Optimize SQL session. Use this page to modify the index, if necessary.
To send indexes to Analyze Impact from Optimize Indexes
© ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center