Optimizable Expressions
The RDM SQL query optimizer is able to optimize a restricted set of relational expressions that are specified in the WHERE clause of a SELECT statement. Simple expressions involving a comparison between a simple column and a literal constant value (or parameter marker or stored procedure argument) can be analyzed by the optimizer to determine if any access methods exist that can retrieve rows satisfying that particular conditional. Expressions for potential use by the optimizer in an execution plan are referred to as optimizable. Table 10 summarizes the optimizable relational expressions.
| 1 | KeyCol1 = constant [AND KeyCol2 = constant]... |
| 2 | FkCol1 = constant [AND FkCol2 = constant]... |
| 3 | FkCol1 = PkCol1 [AND FkCol2 = PkCol2]... |
| 4 | KeyCol1 = Cola [AND KeyCol2 = Colb]... |
| 5 | KeyCol1IN (constant[, constant]...) |
| 6 | KeyCol1 {> | >= | < | <=} constant |
| 7 | KeyCol1 {> | >=} constant [AND KeyCol1 {< | <=} constant] |
| 8 | KeyCol1BETWEEN constantAND constant |
| 9 | KeyCol1 LIKE "pattern" |
The constant is either a literal, a parameter marker ('?'), or a stored procedure argument (if statement is contained in a stored procedure declaration). The KeyColi's refer to the i'th declared column in a given key. The FkColi's (PkColi's) refer to the i'th declared column in a foreign (primary) key. An equality comparison must be provided for all multi-column foreign and primary key columns in order for the optimizer to recognize a join predicate. Cola, Colb, etc., are columns from the same table that match (in type and length) KeyCol1 , KeyCol2, etc., respectively.
These expressions are all written in the following form: ColumnName relop expression. Note that expressions of the form: expression relop ColumnName are recognized and transformed by the optimizer so that the ColumnName is always listed on the left hand side. This transformation may require modification of the relational operator. For example,
select … from … where 1000 > colname
Is changed to
select … from … where colname < 1000
Depending on how the WHERE clause is organized, an expression may or may not be optimizable. Conditional expressions composed in conjunctive normal form are optimizable. In conjunctive normal form, the WHERE clause is constructed as follows:
C1AND C2 AND ... Cn
Each Ci is a conditional expression comprised of a single or multiple OR'ed relational comparisons. Only those Ci's that consist of a single optimizable relational expression are optimizable. In other words, relational expressions that are sub-branches of an OR'ed conditional expression are not optimizable. The best possible optimization results are obtained when the desired conditions use AND. The optimizer can recognize a sequence of OR'ed equality comparisons referencing the same KeyCol1 and will convert it into an IN comparison. For example, the optimizer will convert…
select … from book where bookid = "austen02" or bookid = "cbronte01" or bookid = "dickens07";
into…
select … from book
where bookid in ("austen02", "cbronte01", "dickens07");