Dynamic index (OR strategy)

If the query uses the OR strategy because the query could return duplicate rows, the appropriate indexes are used to retrieve the row IDs for rows that satisfy each or clause. The row IDs for each or clause are stored in a worktable. Since the worktable contains only row IDs, it is called a “dynamic index.” Adaptive Server then sorts the worktable to remove the duplicate row IDs. The row IDs are used to retrieve the rows from the base tables. The total cost of the query includes:

Figure 22-7 illustrates the process of building and sorting a dynamic index for an or query on two different columns.

Figure 22-7: Resolving or queries using the OR strategy

As shown in Figure 22-7, the optimizer can choose to use a different index for each clause.

showplan displays “Using Dynamic Index” and “Positioning by Row IDentifier (RID)” when the OR strategy is used.

See “Dynamic index message (OR strategy)” for more information.

Queries in cursors cannot use the OR strategy, but must perform a table scan. However, queries in cursors can use the multiple matching index scans strategy.

Locking during queries that use the OR strategy depends on the locking scheme of the table.