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.

em-queryanalysistab.png

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.

See Also

Configuration

Information Tab

Tables Tab

Views Tab

Procedures Tab

Type Definitions Tab

Sequences Tab

Permissions & Roles Tabs

SQL Tab




______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.