An or list has as many as N rows of or/in values.The first message shows that an OrScanOp is reading rows from an in-memory table that contain values from an in list or multiple or clauses on the same column. The OrScanOp 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 OrScanOp 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 OrScanOp:
1> select s.id from sysobjects s where s.id in (1, 0, 1, 2, 3) 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). 4 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |NESTED LOOP JOIN Operator (Join Type: Inner Join) | | |SCAN Operator | | FROM OR List | | OR List has up to 5 rows of OR/IN values. | | |SCAN Operator | | 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 OrScanOp puts only the four distinct values in its in-memory table. In the example query plan, the OrScanOp is the left child operator of the NLJoinOp and a ScanOp is the right child of the NLJoinOp. When this plan executes, the NLJoinOp calls the OrScanOp to return a row from its in-memory table, then the NLJoinOp calls on the ScanOp 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.
