Quest Recursive SQL Transformation technology is an innovative AI technology that simulates human SQL transformation technique. It incorporates a set of transformation rules to transform SQL statements on a section-by-section basis. This replaces the trial and error method used by human to rewrite the syntax of a SQL statement. Each transformation rule in the optimization engine is independent from one another, like a capsule; the rule’s capsule can only be opened when all necessary conditions are satisfied (see figure 6). This guarantees the semantic equivalence of the rewritten SQL statements so that they produce the same results as the original SQL. When a SQL statement is transformed by one rule to produce a new SQL syntax, the new syntax may now satisfy the requirements of another rule, hence transformation action is carried out in a recursive manner (see figure 7).

  Figure 6: Recursive Transformation Engine             Figure 7: Chain Effect of SQL Transformation(Insert Image)

Let’s take a look at the following SQL statement and use two of the built-in transformation rules to see how this Recursive Transformation works. We will use one rule which transforms the IN condition to an EXISTS condition and than use another rule that does the reverse, changing the EXISTS condition to an IN condition. We will illustrate this with the following SQL statement.

The first two levels of transformation are shown in the left side of figure 8. SQL statements with syntax different from the original can be produced by following a set of transformation rules. You can see that for each rule applied to the SQL statement, the newly transformed SQL will satisfy another rule. And the order in which the rules are processed can result in different SQL alternatives. In this example, the source SQL has gone through two transformation rules executed in a recursive manner. If we do not stop the recursive transformation, the loop will continue infinitely. A total of 4 unique SQL statements (marked by the solid boxes in Figure 8) are generated by the two transformation rules. If each of these SQL statements ends up with a new execution plan, we potentially have three SQL statements that may give us different performance that can be used as a benchmark to the original SQL statement.

 
Figure 8: A SQL statement being transformed by two recursive transformation rules

Actually, implementing a transformation rule requires a more complicated control than it is shown in this illustration. For example, Quest SQL Optimizer has to check whether a set operator (UNION, MINUS, or INTERSECT) is in a sub-query, whether multiple items are in the SELECT list, and etc…. But the beauty of those rules is that they are self-protected, which means that the transformation and conditional checking are encapsulated into a one rule-capsule to prevent generating incorrect SQL statements (SQL statements that do not produce the same result as the original SQL). In our Recursive Transformation Engine, we have a multitude of transformation rules which can handle very complicated situations. The result of the recursive transformation for a complex SQL statement may exceed what you can imagine. For example, some transformation rules can be applied endlessly to transform a SQL statement to another semantically equivalent statement without limitation, so quotas must be used to control the number of SQL alternatives that are generated. 

Today, with our Recursive SQL Transformation Engine, you can explore ultimate potential that a database SQL optimizer can give to speedup your SQL statements.