In addition to supporting user-supplied selectivity estimates, Sybase IQ also lets users supply additional hint information to the optimizer through a condition hint string. These per-condition hint strings let users specify additional execution preferences for a condition, which the optimizer follows, if possible. These preferences include which index to use for the condition, the selectivity of the condition, the phase of execution when the condition is executed, and the usefulness of the condition, which affects its ordering among the set of conditions executed within one phase of execution.
Condition hint strings, like the user-supplied selectivity estimates, are supplied within the text of the query by wrapping the condition within parentheses. Then within the parentheses and after the condition, you add a comma and a supply a quoted string containing the desired hints. Within that quoted string each hint appears as a hint type identifier, followed by a colon and the value for that hint type. Multiple hints within the same hint string are separated from each other by a comma, and multiple hints can appear in any order. White space is allowed between any of two elements within a hint string.
There are four different supported hint types:
Selectivity hints, which are equivalent to the user-supplied selectivity estimates
The first hint type that can appear within a hint string is a selectivity hint. A selectivity hint is identified by a hint type identifier of either “S” or “s”. Like user-supplied selectivity estimates, the selectivity value is always expressed as a percentage of the table’s rows, which satisfy the condition.
The following example is exactly equivalent to the second example in “User-supplied condition selectivity”.
SELECT * FROM customer c, sales_order o WHERE (c.unpaid_balance > 10000.0, 's: 0.5') AND c.id = o.cust_id
The next supported hint type is an index preference hint, which is identified by a hint type identifier of either “I” or “i”. The value for an index preference hint can be any integer between -10 and 10. The meaning of each positive integer value is to prefer a specific index type, while negative values indicate that the specific index type is to be avoided.
The effect of an index preference hint is the same as that of the INDEX_PREFERENCE option, except that the preference applies only to the condition it is associated with rather than all conditions within the query. An index preference can only affect the execution of a condition if the specified index type exists on that column and that index type is valid for use when evaluating the associated condition; not all index types are valid for use with all conditions. See “INDEX_PREFERENCE option” for the specific meanings of integers between -10 and 10.
The following example specifies a 3 percent selectivity and indicates that, if possible, the condition should be evaluated using an HG index:
SELECT * FROM customer c, sales_order o WHERE (c.unpaid_balance > 8000.0, 'S:3.00, I:+2') AND c.id = o.cust_id
The next example specifies a 37.5 percent selectivity and indicates that if possible the condition should not be evaluated using an HG index:
SELECT * FROM customer c, sales_order o WHERE (c.unpaid_balance > 3000.0, 'i:-2, s:37.500') AND c.id = o.cust_id
The third supported hint type is the execution phase hint, which is identified with a hint type identifier of either “E” or “e”.
Within the Sybase IQ query engine there are four distinct phases of execution where conditions can be evaluated. These phases are named invariant, delayed, bound, and horizontal.
By default, the optimizer chooses to evaluate each condition within the earliest phase of execution where all the information needed to evaluate that condition is available. Every condition. therefore, has a default execution phase where it is evaluated.
Because no condition can be evaluated before the information it needs is available, the execution phase hint can only be used to delay the execution of a condition to a phase after its default phase. It cannot be used to force a condition to be evaluated within any phase earlier than its default phase.
The four phases of condition execution from earliest to latest are described as follows:
Invariant A condition that refers to only one column (or two columns from the same table) and that can be evaluated using an index is generally referred to as a simple invariant condition. Simple invariant condition are normally evaluated early within the optimization process.
This means that the number of rows satisfying all of those invariant conditions is available to guide the optimizer’s decisions on the best join order and join algorithms to use. Because this is the earliest phase of execution, a user can never force a condition into this phase, but conditions can be forced out of this phase into later phases.
Delayed Some conditions cannot be evaluated until some other part of a query has been executed. These delayed conditions are evaluated once when the query node to which they are attached is first fetched. These conditions fall into two categories, uncorrelated subquery conditions and IN or PROBABLY_IN pushdown join conditions created by the optimizer.
Bound Some conditions must be evaluated multiple times. These conditions generally fall into two categories: conditions containing outer references within a correlated subquery, and pushdown equality join conditions created by the optimizer. The outer reference conditions, for example, are reevaluated each time the outer reference value changes during the query's execution.
Horizontal Some conditions, such as those which contain more than two columns from a table, must be evaluated one row at a time, rather than by using an index.
An execution phase hint accepts a values that identifies in which execution phase the user wants the condition to be evaluated. Each value is a case insensitive single character:
D – Delayed
B – Bound
H – Horizontal
The following example shows a condition hint string which indicates that the condition should be moved into the “Delayed” phase of execution, and it indicates that if possible the condition should be evaluated using an LF index.:
SELECT * FROM customer c, sales_order o WHERE (c.unpaid_balance > 10000.0, 'E:D, I:1') AND c.id = o.cust_id AND o.order_price > 5000.0
The final supported hint type is the usefulness hint, which is identified by a hint type identifier of either “U” or “u”. The value for a usefulness hint can be any numeric value between 0.0 and 10.0. Within the optimizer a usefulness value is computed for every condition, and the usefulness value is then used to determine the order of evaluation among the set of conditions to be evaluated within the same phase of execution. The higher the usefulness value, the earlier it appears in the order of evaluation. Supplying a usefulness hint lets users place a condition at a particular point within the order of evaluation, but it cannot change the execution phase within which the condition is evaluated.
The following example shows a condition hint string which indicates that the condition should be moved into the “Delayed” phase of execution, and that its usefulness should be set to 3.25 within that “Delayed” phase.
SELECT * FROM customer c, sales_order o WHERE (c.unpaid_balance > 10000.0, 'U: 3.25, E: D') AND c.id = o.cust_id AND o.order_price > 5000.0
SQL Anywhere Studio does not support user-supplied condition hint strings.
Adaptive Server Enterprise does not support user-supplied condition hint strings.
