具有多个表上的连接的SQL查询性能

When we join more than 2-3 tables in a query, and if we have a common column in all the tables, will there be any difference in the performace when we

当我们在查询中加入超过2-3个表时,如果我们在所有表中都有一个公共列,那么当我们在性能上有所不同时

specify the value to the common columns in all the tables.

指定所有表中公共列的值。

for ex:
```
select e.*
from emp e, dept d
where e.deptno = 10
and d.deptno = 10;
```
give value to one of the common column and join with the other

给一个公共列赋值并与另一个列连接

for ex:
```
select e.*
from emp e, dept d
where e.deptno = 10
and d.deptno = e.deptno;
```

The reason for asking this question is, I have a query(cost is 17), which executes when I specify the values as in example 1 but gets hung and never executes if I join the columns as in example 2.

提出这个问题的原因是,我有一个查询(成本是17),当我在示例1中指定值时执行,但是如果我像示例2中那样加入列,则会挂起并且永远不会执行。

Please help me understand this.

请帮我理解这个。

2 个解决方案

#1

It depends on the indexes. If you have an index on that column in both tables there should be no difference. But if not, the second can be much slower.

这取决于索引。如果两个表中的该列都有索引,则应该没有区别。但如果没有,第二个可能会慢得多。

Is deptno unique? (In either of the tables.) If it is make CERTAIN you set the index that way.

deptno独一无二吗? (在任一表中。)如果是,请设置索引。

#2

I disagree regarding the uniqueness issue. DEPTNO doesn't HAVE to be unique on either table - but if it's not the query may be very slow to respond. Regarding indexes - yes, there should be either an index on DEPTNO alone, or with DEPTNO as the first field on both tables. Without such indexes the query will be very slow.

关于唯一性问题,我不同意。 DEPTNO在任何一个表上都不一定是唯一的 - 但如果不是,查询的响应可能会很慢。关于索引 - 是的,应该单独使用DEPTNO上的索引,或者使用DEPTNO作为两个表上的第一个字段。没有这样的索引,查询将非常慢。

Regarding the query structure - I prefer ANSI query syntax:

关于查询结构 - 我更喜欢ANSI查询语法:

SELECT e.*
  FROM EMP e
  INNER JOIN DEPT d
    ON (d.DEPTNO = e.DEPTNO)
  WHERE e.DEPTNO = 10

I don't understand why the DEPT table is being joined as you're not using any of the data in it, unless there's some possibility that there may be no row in DEPT for DEPTNO=10. Assuming that a row exists in DEPT with DEPTNO=10 you'd get the same results by executing

我不明白为什么DEPT表正在加入,因为你没有使用其中的任何数据,除非DEPTNO = 10的DEPT中可能没有行。假设DEPT中存在DEPTNO = 10的行,您将通过执行获得相同的结果

SELECT e.*
  FROM EMP e
  WHERE e.DEPTNO = 10

without paying the cost of joining DEPT to each result row from EMP - and then turning around and discarding the data from DEPT.

无需支付将DEPT加入EMP的每个结果行的成本 - 然后转向并丢弃DEPT中的数据。

Share and enjoy.

分享和享受。

#1