Oracle SQL IN 语句：这是顺序执行的吗？
TableX
number doc
number item
number parentItem
包含数据：
1, 1000, 0
1, 1010, 1000
1, 1020, 1000
1, 2000, 0
1, 2010, 2000
TableY
number doc
number item
varchar2(16) SomeData
包含数据：
1, 1000, "1000 Data"
1, 2000, "2000 Data"
我使用以下 SQL 查询从 TableY 获取“SomeData”，
select x.doc, x.item, y.SomeData from TableX x
join TableY y
on y.doc = x.doc and y.item IN (x.item, x.ParentItem)
这应该会导致：
1, 1000, "1000 Data"
1, 1010, "1000 Data"
1, 1020, "1000 Data"
1, 2000, "2000 Data"
1, 2010, "2000 Data"
我的问题是：IN-Statement 是按顺序求值的，还是取决于 Oracle 采用的路径？
编辑 我的意思是，当 TableY 中存在来自 TableX 的项目（如 1000）的条目时，该值将首先在 JOIN 中使用还是将首先使用 ParentItem？ 或者只有当该项目上的 JOIN 失败时才完成 ParentItem 上的 JOIN 吗？
TableX
number doc
number item
number parentItem
With data:
1, 1000, 0
1, 1010, 1000
1, 1020, 1000
1, 2000, 0
1, 2010, 2000
TableY
number doc
number item
varchar2(16) SomeData
With data:
1, 1000, "1000 Data"
1, 2000, "2000 Data"
I use the following SQL query to get the "SomeData" from TableY
select x.doc, x.item, y.SomeData from TableX x
join TableY y
on y.doc = x.doc and y.item IN (x.item, x.ParentItem)
Which should result in:
1, 1000, "1000 Data"
1, 1010, "1000 Data"
1, 1020, "1000 Data"
1, 2000, "2000 Data"
1, 2010, "2000 Data"
My question is : Is the IN-Statement evaluated sequentially, or is this depending on the path Oracle takes?
Edit
What I mean when there an entry in TableY for an item from TableX (like 1000), will this value be used first in the JOIN or will the ParentItem be used first?
Or is the JOIN on ParentItem on only done when the JOIN on the item fails ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一般来说，在使用数据库时，除非您指定一个顺序，否则没有顺序。我相信
IN总是检查整个列表，而不是在找到匹配项时短路，但不能保证列表的处理顺序（如果检查整个列表，这并不重要） ）。根据您修改后的问题：
Oracle 无法根据行包含的内容创建执行计划，因此优化器将制定一个计划，以尽可能最有效的方式查找两列。该计划的内容很大程度上取决于表的大小和您创建的索引。
一次处理一个表几乎总是更快，因此优化器可能会选择一种可以轻松检查两列的路线，而不是必须一次检查一个列的路线。这意味着，除非有更好的途径，否则很可能会进行全表扫描。一些实验表明，即使有一个索引覆盖两列，优化器也会选择全表扫描。
有趣的是，这似乎是少数最好将两列单独索引的情况之一。如果两列上都有单独的索引，则优化器似乎会扫描这两个索引，然后使用位图或从每个结果集中获取唯一的 rowid 集。
这里需要注意的是，我的研究是在我可用的大型表格上使用高度人工场景完成的。您应该以最简单、最容易阅读（和维护）的方式构建查询，然后测试它的实际性能并自行查看它的解释计划。仅当您确定存在（或可能存在）性能问题时，您才应该担心寻找另一种更有效（但可能不那么自解释）的查询编写方式。一般来说，如果您有一个经过深思熟虑的sargable查询，优化器将做好选择最有效的可用路径的工作。
In general, when working with databases there is no order unless you specify one. I believe
INalways examines the entire list rather than short-circuiting when it finds a match, but there's no guarantee about the order the list will be processed in (not that it matters if it's checking the entire list).Based on your revised question:
Oracle can't create an execution plan based on what the rows contain, so the optimizer will develop a plan that looks for both columns in the most efficient way possibly. What that plan will be will depend largely upon the size of your tables and the indexes you've created.
It's almost always faster to process a table once, so the optimizer is likely to choose a route where it can easily examine both columns over one where it has to examine the columns one-at-a-time. This means that, unless it has a better avenue, a full-table scan is likely. A little experimentation shows me that even if there's an index covering both columns, the optimizer will opt for the full-table scan.
Interestingly, this appears to be one of the few scenarios where it may be better to have the two columns indexed separately. If there are separate indexes on both columns, it seems the optimizer will scan both indexes, then use a
bitmap orto get the unique set of rowids from each result set.The caveat here, is that my research was done on large tables I had available, using highly artificial scenarios. You should build your query in the simplest, easiest to read (and maintain) way you can, then test it's actual performance and look at it's explain plan for yourself. Only if you've determined that there is performance problem (or there is likely to be) should you worry about finding another, more efficient (but perhaps less self-explanatory) way of writing the query. In general, if you've got a well-thought-out, sargable query, the optimizer will do a good job of picking the most efficient path available.
评估的顺序并不重要。 IN 语句只是表示
y.item = x.item OR y.item = x.ParentItem OR ...的简写方法。The order of evaluation doesn't matter. An IN statement is simply a short-hand method of saying
y.item = x.item OR y.item = x.ParentItem OR ....