Enterprise Manager Java App: Database - Query Analysis Tab
In BBj 13.0 and higher, the Enterprise Manager Java app has been superseded by a new browser Enterprise Manager and Eclipse plug-in. See Enterprise Manager - Database: Query Analysis.
The Query Analysis tab provides an interface for the administrator to analyze information about all of the queries run on the selected database. This information can be used to help determine if the database tables are indexed in a way that allows the SQL engine to effectively optimize the most common queries run against the database.
In BBj 12.0 and higher, the administrator determines that it would be beneficial to add or remove an index on a particular combination of columns in the list, this can be done right from the user interface.
Specify the filter criteria for the tables in the "Table Name Filter" field and press the [Filter] button.
Each row in the GUI table displays the following information:
|
Option |
Description |
|
Table Name |
Name of the table. |
|
Column List |
List of columns (in index segment order) that were included in the WHERE clause that could have possibly been used for optimization if they were indexed. |
|
Score |
A score that relates to the how often this combination of columns was used. |
|
Indexed |
Whether this combination of columns has an index on them or not. |
When a statement is executed that includes a WHERE clause, it updates the underlying information used to populate this table. For example, the following statement will update information for the "LAST_NAME, FIRST_NAME", "FIRST_NAME, LAST_NAME", "FIRST_NAME", and "LAST_NAME" items in the list:
SELECT * FROM customer WHERE last_name = 'Doe' and first_name = 'John'
Adding/Removing an Index
In BBj 12.0 and higher, use the query analysis information to determine
where new indices might be needed or where indices could be removed. The
user interface makes it very easy to add or remove indices:
1. Select the item in the list which shows the combination of columns to
be indexed.
2. Right-click on the item and select [Create Index] or [Drop Index]
Note - adding or removing indexes can take a significant amount of time to complete depending on the number of records in the table's data file.