Visual SQL Tuning (VST)

Visual SQL Tuning (VST) is a ground breaking technology that we are aggressively developing and improving every verison. In this first release of DB Optimizer with VST , version 2.0, we already benefit for quick visual identification of issues in the schema and SQL queries if they exist.

In the image below is an example of VST diagram in the top right. The table INVESTMENT lacks a join to the other tables and for every row returned from the main part of the query (CLIENT_TRANSACTION , BROKER, OFFICE_LOCATION) every row of INVESTMENT will be returned resulting in a Cartesian product. Clearly a problem in the query. (We provide possible re-writes solutions for this kind of problem under the “Overview”, not shown)

Below is an image of a more subtle cases that the VST clearly highlights where all the tables are joined but there is an implied Cartesian. As long as there are more than one row satisfying the join between the first join CLIENT and BROKER and second join BROKER and CLIENT_TRANSACTION we will again return Cartesian product of rows. This is clear in the diagram as the table BROKER has two details tables above it. Details are always above Master tables and there should only be at most one detail table above any Master tables. This could be a flaw in the query or a flaw in the schema design:

For example a well formed query would look like:

If Master Detail information is missing then the VST diagram will have many-to-many connectors:

This same set of data with properly defined unique indexes or primary key and foreign key definitions would look like:

And in fact the optimizer more consistently correctly optimizes the query when the schema definitions are well defined, and thus the query runs faster more consistently. On Oracle for example, the bottom query was always correctly optimized, where the one above without any unique indexes was incorrectly optimized in many cases