代码之家 › 专栏 › 技术社区 › Jason Baker

两列上的左外部联接性能问题

left-join oracle10g join oracle sql

Jason Baker · 技术社区 · 16 年前

我使用的SQL查询类似于以下表单:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
AND table1.period = table2.period

要么速度太慢,要么是死锁,因为至少需要4分钟才能返回。如果我将其更改为:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
WHERE table1.period = table2.period

然后它就可以正常工作了(尽管没有返回正确的列数)。有没有办法加快速度?

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.period = table2.period
WHERE table1.person_uid = table2.person_uid

更新2: 这些实际上是我要加入的观点。不幸的是,它们位于我无法控制的数据库上,因此我无法(轻松地)对索引进行任何更改。不过,我倾向于同意这是一个索引问题。在接受答案之前,我会等一会儿,以防有什么神奇的方法来调整这个我不知道的问题。否则,我将接受当前的一个答案,并尝试找出另一种方法来做我想做的事情。谢谢大家到目前为止的帮助。

8 回复 | 直到 16 年前

cletus 16 年前

请记住,语句2和3与第一个语句不同。

怎样好吧,你在做一个左外连接,你的WHERE子句没有考虑到这一点(就像ON子句一样)。至少,请尝试:

SELECT col1, col2
FROM table1, table2
WHERE table1.person_uid = table2.person_uid (+)
AND table1.period = table2.period (+)

看看你是否也遇到同样的性能问题。

这些表上有什么索引?此关系是否由外键约束定义?

HLGEM 15 年前

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
WHERE table2.person_uid is null

在这种情况下,您需要第二个表中没有记录的记录。但除了这种特殊情况,如果在where子句中引用表2中的字段,则将左连接更改为内部连接。

如果您的查询不够快,我会查看您的索引。

Dave Costa 16 年前

任何人根据你提供的信息告诉你的都是猜测。

查看查询的执行计划。如果你看不到计划缓慢的原因,请将计划张贴在此处。

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

Andrew Rollings 16 年前

你们有覆盖索引吗 person_uid 和 period 两张桌子都要吗?

如果没有,请添加它们并重试。

看看执行计划,看看查询实际上在做什么。

另外:字段的数据类型是什么?两张桌子上的都一样吗?隐式演员阵容确实可以让事情变慢。

Paul Tomblin 16 年前

这些表在要连接的列上有索引吗?安装Oracle的免费SQLDeveloper产品,并使用它对该查询进行“解释”,查看是否对这两个表进行顺序扫描。

MatBailie 16 年前

在左连接中,您将扫描表1中的每个唯一组合(person\u uid,period),然后搜索表2中的所有对应记录。如果表2没有适当的索引,这也可能涉及扫描整个表。

在没有看到执行计划的情况下,我的最佳猜测是,第一个查询(似乎唯一正确的查询)必须扫描表2和表1。

SELECT
   col1, col2
FROM
   table2
FULL OUTER JOIN
   table1
      ON table1.person_uid = table2.person_uid
      AND table1.period = table2.period
WHERE
   table1.person_uid IS NOT NULL

这里的希望是,您可以扫描表2中的每个唯一组合(person\u uid,period),但要使用表1中的索引。(与扫描表1和使用表2上的索引不同,这正是我从您的查询中所期望的。)

但是,如果表1没有适当的索引,您将很难看到任何性能改进。。。

德姆斯。

HLGEM 8 年前

BobC 8 年前

ANSI连接语法在连接条件和过滤器谓词之间提供了非常明确的区别;这在编写外部联接时非常重要。使用emp/dept表,查看以下两个外部联接的结果

SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
LEFT OUTER JOIN emp e
on  d.deptno = e.deptno
and loc in ('NEW YORK','BOSTON' )
;

DNAME              DEPTNO ENAME             MGR LOC
-------------- ---------- ---------- ---------- -------------
ACCOUNTING             10 CLARK            7839 NEW YORK
ACCOUNTING             10 KING                  NEW YORK
ACCOUNTING             10 MILLER           7782 NEW YORK
RESEARCH               20                       DALLAS
SALES                  30                       CHICAGO
OPERATIONS             40                       BOSTON

Q2
SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
LEFT OUTER JOIN emp e
on  d.deptno = e.deptno
where loc in ('NEW YORK','BOSTON' )
;

DNAME              DEPTNO ENAME             MGR LOC
-------------- ---------- ---------- ---------- -------------
ACCOUNTING             10 CLARK            7839 NEW YORK
ACCOUNTING             10 KING                  NEW YORK
ACCOUNTING             10 MILLER           7782 NEW YORK
OPERATIONS             40                       BOSTON

Q1显示的第一个示例是“连接常数”的示例。本质上,过滤条件是在执行外部联接之前应用的。因此,您将删除行,这些行随后作为外部联接的一部分添加回。这不一定是错的,但这是你真正要求的问题吗?通常需要Q2中显示的结果,在(外部)连接之后应用过滤器。

对于大型数据集,也存在性能影响。在许多情况下,常量的连接必须由优化器通过创建横向视图在内部解决,横向视图通常只能通过嵌套循环连接而不是散列连接进行优化

对于熟悉Oracle外部联接语法的开发人员来说,查询可能是按照以下方式编写的

SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
        ,emp e
where  d.deptno = e.deptno(+)
and loc in ('NEW YORK','BOSTON' )

此查询在语义上与上面的Q2相同。

总之,在编写ANSI外部联接时,理解JOIN子句和WHERE子句之间的区别是非常重要的。