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。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9925929/viewspace-976729/ ，如需转载，请注明出处，否则将追究法律责任。
