Using Dynamic Index.
The term dynamic index refers to a worktable of row IDs used to process some queries that use or clauses or an in (values list) clause. When the OR strategy is used, Adaptive Server builds a list of all the row IDs that match the query, sorts the list to remove duplicates, and uses the list to retrieve the rows from the table.
For a full explanation, see Performance and Tuning Guide: Optimizer.
For a query with two SARGs that match the two indexes (one on au_fname, one on au_lname), the showplan output below includes three “FROM TABLE” sections:
The first two “FROM TABLE” blocks in the output show the two index accesses, one for the first name “William” and one for the last name “Williams”.
These blocks include the output “Index contains all needed columns,” since the row IDs can be retrieved from the leaf level of a nonclustered index.
The final “FROM TABLE” block shows the “Using Dynamic Index” output and “Positioning by Row IDentifier (RID).”
In this step, the dynamic index is used to access the data pages to locate the rows to be returned.
select au_id, au_fname, au_lname
from authors
where au_fname = "William"
or au_lname = "Williams"
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
authors
Nested iteration.
Index : au_fname_ix
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
au_fname ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
FROM TABLE
authors
Nested iteration.
Index : au_lname_ix
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
au_lname ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
FROM TABLE
authors
Nested iteration.
Using Dynamic Index.
Forward scan.
Positioning by Row IDentifier (RID).
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
