嵌套内联查询比平等联接更快 [英] Nested inline query faster than Equal Join
问题描述
假设有两个表:
TRANSACTION
Primary Key: REF_NO
Columns: REF_NO, TXN_DATE, ITEM_CODE, QUANTITY
ITEM
Primary Key: ITEM_CODE
Columns: ITEM_CODE, ITEM_DESC
查询(1):
SELECT T.REF_NO, T.TXN_DATE, T.ITEM_CODE,
I.ITEM_DESC,
T.QUANTITY
FROM TRANSACTION T, ITEM I
WHERE T.ITEM_CODE = I.ITEM_CODE
查询(2):
SELECT T.REF_NO, T.TXN_DATE, T.ITEM_CODE,
(SELECT ITEM_DESC FROM ITEM WHERE ITEM_CODE = T.ITEM_CODE) AS ITEM_DESC,
T.QUANTITY
FROM TRANSACTION T
两个表上都根据需要包含索引(索引).
Indices (indexes) are on both tables as necessary.
上面是我正在做的事情的非常简化的版本，但是概念是相同的.
The above is a very simplified version of the stuff I'm doing, but the concept is the same.
有人告诉我(1)由于有索引而效率更高，而解释计划实际上表明确实如此. (1)的解释计划显示两个表上的索引访问. (2)的解释计划显示对ITEM的索引访问，但对TRANSACTION的全表访问.
I was told that (1) is more efficient due to indices, and Explain Plan actually suggests that it is. Explain plan for (1) shows index access on both tables. Explain plan for (2) shows index access on ITEM, but full table access on TRANSACTION.
但是我的难题是，当我在大量数据上运行它们以计时实际性能时，(2)比(1)快四倍！可能的原因是什么?为什么我应该选择(1)而不是(2)? (我们决定在(1)中选择(2).)
But my dilemma is when I run them on a very large set of data to time the actual performance, (2) is four times faster than (1)! What are the possible reasons for this? Why should I choose (1) over (2)? (We decided to choose (2) over (1).)
推荐答案
您最有可能从标量子查询缓存中受益.我最近在博客上发表了有关Oracle 11g(或10g?)的这一令人敬畏的功能的补充:
You most likely profited from Scalar Subquery Caching. I've recently blogged about this awesome feature addition to Oracle 11g (or 10g?):
http://blog.jooq.org/2011 /09/02/oracle-scalar-subquery-caching
检查执行计划，您会在计划的顶部发现一些奇怪的元素，这表明并不是对TRANSACTIONS表中的每一行都真正地评估了实际的子查询.这是因为 Oracle的CBO内省约束元数据推断出，如果使用ITEM_CODE等价联接，则TRANSACTIONS中的每一行只能有一个匹配的行.这些知识使您的子查询易于缓存.如果TRANSACTIONS.ITEM_CODE的值相等，那么缓存会产生非常积极的效果.
Check out your execution plan, you'll find some curious elements at the top of the plan, indicating that the actual subquery is not really evaluated for every row originating from the TRANSACTIONS table. This is because Oracle's CBO introspects constraint metadata to deduce that for every row in TRANSACTIONS there can only be one matching row in ITEMS, if equi-joined using ITEM_CODE. This knowledge makes your subquery subject to caching. If you have many equal values for TRANSACTIONS.ITEM_CODE, caching can have a very positive effect.
更多有用的信息可以在这里找到:
More useful information can be found here:
- http://www.oracle .com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html
- 是否存在与DETERMINISTIC类似的PL/SQL编译指示，但仅适用于单个SQL SELECT的范围?
- http://www.scribd.com /doc/28708969/Metadata-Matters-by-Tom-Kyte-Oracle
- http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html
- Is there a PL/SQL pragma similar to DETERMINISTIC, but for the scope of one single SQL SELECT?
- http://www.scribd.com/doc/28708969/Metadata-Matters-by-Tom-Kyte-Oracle
这篇关于嵌套内联查询比平等联接更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
