INF: Why SHOWPLAN Shows Query Using "Dynamic Index"

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

The SET SHOWPLAN ON command can be used to help analyze how SQL Server's query optimizer is producing an access plan for returning data. This access plan includes the order in which the optimizer joins tables and what, if any, available indexes it will make use of. With some queries, the output from SHOWPLAN will show that the optimizer is accessing the data using a "dynamic index." This is part of the optimizer's "OR strategy": that is, how it optimizes access plans for queries involving one or more OR conditions in the WHERE clause.

MORE INFORMATION

Queries involving OR clauses are generally not very efficient in terms of accessing the data. For example, suppose a report is needed that includes all books from the Titles table that either are of type "business", or have a price greater that $20. Assume that the Titles table in this example is a large table with a nonclustered index on "type" and a nonclustered index on "price". The following query could be used to get the desired results:

   SELECT title_id, type, price
   FROM pubs..titles
   WHERE type = 'business'
   OR price > $20

The second method from the above scenario is very similar to SQL Server's OR strategy. The query optimizer breaks the WHERE clause into its component OR clauses and evaluates them separately. It then places all the qualifying row IDs in a work table, sorts the table to remove any duplicate rows, and uses the remaining row IDs to quickly retrieve the correct rows from the data pages. The final work table containing the qualifying row IDs is termed the dynamic index. The row IDs are an internal representation of the page number and the row number on that page of the given row.

To illustrate the above process, assume the following is a subset of the Titles table (the "row ID" is the internal row ID of each row, not an actual column of the table).

   Row ID     Title_id   Type        Price
   ---------------------------------------
      1       BU9033     Business    19.00
      2       PS3840     Psychology  28.00
      3       CS1011     Computers   16.00
      4       BU8845     Business    32.00
      5       CO5490     Cooking     14.00
      6       BU7349     Business    26.00
      7       HL7204     Health      41.00
      8       PS2099     Psychology  17.00

   row ID 1
   row ID 4
   row ID 6

   row ID 2
   row ID 4
   row ID 6
   row ID 7

   row ID 1
   row ID 2
   row ID 4
   row ID 4 <--duplicate removed
   row ID 6
   row ID 6 <--duplicate removed
   row ID 7

SQL Server does not always resort to using the OR strategy for every query that contains OR clauses. The following conditions must be met before it will choose to use the OR strategy:

• All columns in the OR clause must belong to the same table.
• If any portion of the OR clause requires a table scan (due to lack of an index or poor selectivity of a given index), a table scan will be used for the entire query, rather than the OR strategy.
• The decision to use the OR strategy is made after all indexes and costs are evaluated. If any other access plan is less costly (in terms of page I/Os), SQL Server will choose to use the plan with the least cost. In the example above, if a straight table scan would result in fewer page I/Os than using the OR strategy, the query would be processed as a table scan instead of using the dynamic index.

   SELECT *
   FROM pubs..titles
   WHERE title_id IN ('BU9033', 'CO5490', 'PS7732')

   SELECT *
   FROM pubs..titles
   WHERE (title_id = 'BU9033'
   OR title_id = 'CO5490'
   OR title_id = 'PS7732')