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.
