FROM or LIST

An OR list has as many as N rows; one for each distinct OR or IN value specified in the query.The first message shows that an OR scan is reading rows from an in-memory table that contains values from an IN list or multiple or clauses on the same column. The OR list appears only in query plans that use the special or strategy for in lists. The second message shows the maximum number of rows (N) that the in-memory table can have. Since OR list eliminates duplicate values when filling the in-memory table, N may be less than the number of values appearing in the SQL statement. As an example, the following query generates a query plan with the special or strategy and an OR list:

select s.id from sysobjects s where s.id in (1, 0, 1, 2, 3)
go

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
    The type of query is SELECT.

    4 operator(s) under root

    ROOT:EMIT Operator (VA = 4)

    |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
    |
    |   |SCAN Operator (VA = 2)
    |   | FROM OR List
    |   | OR List has up to 5 rows of OR/IN values.
    |
    |   |RESTRICT Operator (VA = 2)(0)(0)(0)(8)(0)
    |   |   |SCAN Operator (VA = 1)
    |   |   | FROM TABLE
    |   |   | sysobjects
    |   |   | s
    |   |   | Using Clustered Index.
    |   |   | Index : csysobjects
    |   |   | Forward Scan.
    |   |   | Positioning by key.
    |   |   | Index contains all needed columns. Base table will not be read.
    |   |   | Keys are:
    |   |   | id ASC
    |   |   | Using I/O Size 2 Kbytes for index leaf pages.
    |   |   | With LRU Buffer Replacement Strategy for index leaf pages.

This example has five values in the IN list, but only four are distinct, so the OR list puts only the four distinct values in its in-memory table. In the example query plan, the OR list is the left-child operator of the NESTED LOOP JOIN operator and a SCAN operator is the right child of the NESTED LOOP JOIN operator. When this plan executes, the NESTED LOOP JOIN operator calls the or command to return a row from its in-memory table, then the NESTED LOOP JOIN operator calls on the SCAN operator to find all matching rows (one at a time), using the clustered index for lookup. This example query plan is much more efficient than reading all of the rows of sysobjects and comparing the value of sysobjects.id in each row to the five values in the IN list.