INF: Why SHOWPLAN Shows Query Using "Dynamic Index"

ID: Q89387

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2x

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

There are two ways that the desired data could be obtained. One way is to start at the first row of the table, check its type and price, and if either match the criteria, return that row, then move to the next row and do the same, and so on. The other option is to first find all the rows that match the first criteria of "type = 'business'" and store them in an intermediate table, then find all the rows that meet the second criteria of "price > $20", and store those rows in the intermediate table. Since it's possible (and likely) that a business book may cost more than $20, there's a chance of having duplicate rows in the intermediate table, so the intermediate table would need to be searched to eliminate duplicates, and the remaining rows would be the desired results.

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

If the SQL Server query optimizer decided to evaluate the query using the OR strategy, it would first use the index on the "type" column to retrieve all row IDs that satisfy the first clause (type='business'):


   row ID 1
   row ID 4
   row ID 6

It then could use the index on the "price" column to retrieve all row IDs that satisfy the second clause (price > $20):


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

The results of these two searches are combined in a single work table, the row IDs are sorted, and duplicates are removed:


   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

Finally, SQL Server can quickly scan the work table and retrieve the rows from the data pages with the given row IDs.

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.

Note: SQL Server interprets queries that use the IN clause as though they were ORs. For example, assume the following query is used to get title information:


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

SQL Server will break this query down so that it is equivalent to the following:


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

The query optimizer can then evaluate the cost of using the OR strategy as an access plan for this query.

Additional query words: Windows NT

Keywords : kbother SSrvServer SSrvWinNT
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS
Issue type :