Getting Started › Overriding SQL Key Selection

Overriding SQL Key Selection

Although the SQL Optimizer normally automatically selects the most efficient key for you, occasionally the statistics used to estimate costs do not reflect actual costs closely enough to select the most efficient key. When this occurs, you can specify the key to be used in a special format of a correlation name, or if the SQL statement is generated and you cannot specify a correlation name, in a synonym name using the same special format.

Following is the syntax of the override key option:

►►─ x_HINT_keynm ─────────────────────────────────────────────────────────────►◄

x

is any value that is meaningful to you.

Valid Entries:

any leading characters

Default Value:

(No default)

_HINT_

is the literal that triggers the interpretation of the next five characters as the override key.

Valid Entries:

_HINT_

Default Value:

(No default)

keynm

is the override key, a 5-character internal key name, not the SQL key name. If this override key name exists for the table, all other keys are marked "ignore" in the Compound Boolean Selection Optimization Report, and all other keys are hidden from the SQL Optimizer. Be aware, however, that if the specified key name does not exist or cannot be used because it does not index nil values, no error is generated and the key is ignored.

No check is made to qualify the key for performance.

That the key was used can be reported by Accounting Element CBSOR (CBS Optimizer Reasons) as key type "P," that is, Parm, because it is specified in the CBS RQA (Request Qualification Area) Parameter section as the override key. The SQL Optimization Report also indicates the use of the override key under an "OVERRIDE KEY" heading.

Valid Entries:

a valid 5-character internal key name (not the SQL name for the key)

Default Value:

(No default)