INDEX_PREFERENCE option

Function

Controls the choice of indexes to use for queries.

Allowed values

-10 to 10

Default

0

Scope

DBA permissions are not required to set this option. Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.

Description

The Sybase 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. Table 2-12 describes the valid values for this option and their action.

Table 2-12: INDEX_PREFERENCE values

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