INDEX_PREFERENCE
Option
Controls the choice of indexes to use for queries.
| Value | Action |
|---|---|
| 0 | Let the optimizer choose |
| 1 | Prefer LF indexes |
| 2 | Prefer HG indexes |
| 3 | Prefer HNG indexes |
| 4 | Prefer CMP indexes |
| 5 | Prefer the default index |
| 6 | Prefer WD indexes |
| 8 | Prefer DATE indexes |
| 9 | Prefer TIME indexes |
| 10 | Prefer DTTM indexes |
| -1 | Avoid LF indexes |
| -2 | Avoid HG indexes |
| -3 | Avoid HNG indexes |
| -4 | Avoid CMP indexes |
| -5 | Avoid the default index |
| -6 | Avoid WD indexes |
| -8 | Avoid DATE indexes |
| -9 | Avoid TIME indexes |
| -10 | Avoid DTTM indexes |
0
Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. At the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
The SAP IQ optimizer normally chooses the best index available to process local WHERE clause predicates and other operations that can be done within an IQ index. INDEX_PREFERENCE is used to override the optimizer choice for testing purposes; under most circumstances, it should not be changed.