2

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.

Savitha
  • 405
  • 4
  • 15
  • 25

2 Answers2

2

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.

Ariel
  • 25,995
  • 5
  • 59
  • 69
  • deptno is not unique but is a part of composite PK and so index exists on that column. I even gathered the statistics but still no help. – Savitha Aug 28 '12 at 09:16
  • @Savitha Is it the first column in the composite PK? Because otherwise it will do nothing. – Ariel Aug 28 '12 at 09:17
  • @Savitha Wait - it's not unique? In both tables? It has to be unique in at least one of them, otherwise your query will explode into a huge number of rows. – Ariel Aug 28 '12 at 09:18
  • No, its neither the first column in the composite PK nor is unique in both the tables. But the same query(example 2) was working two months back. AFter that there was some change in the DB level and the problem started. Many of the indexes were dropped that time. I had re-created them. Gathered statistics by DBMS_STATS.GATHER_TABLE_STATS(). But still the query gets hung. – Savitha Aug 28 '12 at 09:56
  • @Savitha Of course it gets hung. To work the column MUST be first in the composite index. Create an extra index for it if necessary, or modify the existing one. And it MUST be unique (by itself - not composite) in at least one of the tables (it doesn't have to be both). – Ariel Aug 28 '12 at 10:03
1

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.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • DEPTNO needs to be unique to avoid a cartesian join where the number of rows that match 10 in one table is multiplied by the number in the other table. This can result in millions of rows being returned despite only having thousands in the database. Sometimes you actually want that, but not in this case. – Ariel Aug 28 '12 at 21:59
  • @Ariel - I agree that this it only makes sense for DEPTNO to be unique somewhere (I'd certainly expect it to be unique in the DEPT table), but the query will still execute correctly, even if it produces a Cartesian join. I suppose that the query could be rewritten as `SELECT e.* from (SELECT DISTINCT DEPTNO FROM DEPT) d INNER JOIN EMP e ON (e.DEPTNO = d.DEPTNO) WHERE d.DEPTNO = 10` - but I'm still at a loss as to why DEPT is being joined in the first place. And so it goes... – Bob Jarvis - Слава Україні Aug 29 '12 at 01:55
  • @Bob: I took the example of EMP and DEPT just to explain my scenario. This scenario is observed in my original query, in which 4 tables are joined. – Savitha Aug 31 '12 at 10:40