oracle 外连接的有效替代方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/783660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Efficient Alternative to Outer Join
提问by Steven
The RIGHT JOINon this query causes a TABLE ACCESS FULLon lims.operator. A regular join runs quickly, but of course, the samples 'WHERE authorised_by IS NULL' do not show up.
在RIGHT JOIN此查询导致TABLE ACCESS FULL的lims.operator。常规连接运行得很快，但当然，样本“ WHERE authorised_by IS NULL”不会出现。
Is there a more efficient alternative to a RIGHT JOINin this case?
RIGHT JOIN在这种情况下，是否有更有效的替代方法？
SELECT full_name
FROM (SELECT operator_id AS authorised_by, full_name
FROM lims.operator)
RIGHT JOIN (SELECT sample_id, authorised_by
FROM lims.sample
WHERE sample_template_id = 200)
USING (authorised_by)
NOTE: All columns shown (except full_name) are indexed and the primary key of some table.
注意：显示的所有列（full_name 除外）都已编入索引，并且是某个表的主键。
回答by DCookie
Since you're doing an outer join, it could easily be that it actually is more efficient to do a full table scan rather than use the index.
由于您正在执行外部联接，因此很容易执行全表扫描而不是使用索引实际上更有效。
If you are convinced the index should be used, force it with a hint:
如果您确信应该使用索引，请使用提示强制使用它：
SELECT /*+ INDEX (lims.operator operator_index_name)*/ ...
SELECT /*+ INDEX (lims.operator operator_index_name)*/ ...
then see what happens...
然后看看会发生什么……
回答by BQ.
No need to nest queries. Try this:
无需嵌套查询。尝试这个：
select s.full_name
from lims.operator o, lims.sample s
where o.operator_id = s.authorised_by(+)
and s.sample_template_id = 200
回答by Stefan Steinegger
I didn't write sql for oracle since a while, but i would write the query like this:
我有一段时间没有为 oracle 写 sql，但我会写这样的查询：
SELECT lims.operator.full_name
FROM lims.operator
RIGHT JOIN lims.sample
on lims.operator.operator_id = lims.sample.authorized_by
and sample_template_id = 200
Does this still perform that bad?
这仍然表现得那么糟糕吗？
