ITPub博客

首页 > IT职业 > IT生活 > 学习笔记--how the cbo evaluates IN-list iterators

学习笔记--how the cbo evaluates IN-list iterators

原创 IT生活 作者:OmarChina 时间:2007-10-15 15:12:02 0 删除 编辑
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。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9925929/viewspace-976729/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    68
  • 访问量
    997067