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:
The sum of the index accesses, that is, for each or clause, the cost of using the index to access the row IDs on the leaf pages of the index (or on the data pages, for a clustered index on an allpages-locked table)
The cost of reading the worktable and performing the sort
The cost of using the row IDs to access the data pages
Figure 5-7 illustrates the process of building and sorting a dynamic index for an or query on two different columns.
Figure 5-7: Resolving or queries using the OR strategy
As shown in Figure 5-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)” on page 107 in the Performance and Tuning: Monitoring and Analyzing for Performance book 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.
