學習筆記--how the cbo evaluates IN-list iterators

in-list iterators就是查詢時候用IN子句。[@more@]SELECT header_id, line_id, revenue_amount
FROM so_lines_all
WHERE header_id IN (1011,1012,1013);
SELECT header_id, line_id, revenue_amount
FROM so_lines_all
WHERE header_id = 1011
OR header_id = 1012
OR header_id = 1013;
Plan
-------------------------------------------------
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX RANGE SCAN SO_LINES_N1

這兩句SQL的執行計劃一樣。兩者是等價的。

如果索引是個UNIQUE。用IN的方式發生的是RANGE SCAN不會是一個UNIQUE的SCAN的

SELECT header_id, line_id, revenue_amount
FROM so_lines_all
WHERE line_id IN (1011,1012,1013);
Plan
-------------------------------------------------
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX RANGE SCAN SO_LINES_U1

IN-LIST操作在NESTED LOOP中

Example 2–4 IN-List Iterator with a Nested Loop
SELECT h.header_id, l.line_id, l.revenue_amount
FROM so_headers_all h, so_lines_all l
WHERE l.inventory_item_id = :b1
AND h.order_number = l.header_id
AND h.order_type_id IN (1,2,3);
Plan
-------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX RANGE SCAN SO_LINES_N5
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX RANGE SCAN SO_HEADERS_U2

IN-LIST操作特別有用if there is an expensive first step that you do
not want to repeat for every IN-list element.

他沒有自己特定的提示HINT。可以使用INDEX。