问题描述:

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

  1. 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;

  2. 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.

Please help me understand this.

网友答案:

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.

网友答案:

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.

Regarding the query structure - I prefer ANSI query syntax:

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

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.

Share and enjoy.

相关阅读:
Top