Basic Language Elements › Column Names › Correlation Names › SQL Index Binding

SQL Index Binding

Beginning in r11, SQL binds to a specific key more frequently than in previous versions. The purpose of this is to reduce the cost of key selection optimization in Compound Boolean Selection at execution.

You can tell if a key was selected during binding by the SQL Optimizer in the Compound Boolean Selection (CBS) Diagnostic Report when the KEY parameter specifies a key name. This is also reflected in the CBSOR Accounting Element.

You may directly specify the key to use in your query by appending "_HINT_keynm" to the correlation name, where keynm is the 5-character internal key name that is printed in the SQL Optimization and CXX Reports.

Note: If the 5-character key name is not found or cannot be used due to KEY_INC = N (that is, nil values not indexed), the query is processed as if no HINT key had been given.

In the following example, assume that:

• CARS is the table name.
• COLOR is the 5-character key name (keynm).
• T1 is the variable chosen to prefix _HINT_keynm, that is, in this specific example T1 is the prefix of the SQL identifer that composes the correlation name, resulting in T1_HINT_COLOR.
• Indexes exist for COLOR and MAKE.

If there are more MAKE values than COLOR values, the SQL Optimizer may select the MAKE index, but in the case of this example, because the COLOR is so rare, it is the best key to use.

Following is the coding specific to this example, given the previous explanation.

 SELECT *
 FROM CARS T1_HINT_COLOR
 WHERE T1_HINT_COLOR.COLOR = "PINK"
   AND T1_HINT_COLOR.MAKE = "FORD";