dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: outer join

  1. #1
    Join Date
    Sep 2002
    Posts
    40

    Question outer join

    I always hear
    Outer joins are typically expensive in terms of performance can someone explain this more explicitly as to why ?
    Last edited by smimran; 01-04-2003 at 01:13 AM.
    OCP DBA 7.3,8,8i,9i

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    oh dear i sense the flame throwers are being warmed up for another ocp thread.......smimran have u benchmarked this? tried through sqlplus with at least set timing on?

    steve
    Last edited by stmontgo; 01-04-2003 at 01:03 AM.
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    In simple terms, YES, becuase it has to fetch those records as well which have no matching records in other tables. I will leave it to you to analyze it technically.
    Agasimani
    OCP(10g/9i/8i/8)

  4. #4
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    Dear Sir

    I am making some risky statements Experts do correct me
    I have undertaken various conditions for EMP & DEPT tables and observed Explain plans. I am trying to compare Equi-join and Outer-join.

    My observerations as follows

    1. When No statistics are generated and
    No Indexes are created on Joining columns
    then
    Outer Joines are heavier than Equi-joins

    2. When u have statistics in place
    irrespective of whether u have indexes on columns or no
    Both Joins carry equal weight.

    Queries I have playing around are run on Oracle 9.0.1.1.1
    For equi-join
    -------------------------
    select b.deptno , b.dname
    from emp a , dept b
    where
    a.deptno=b.deptno
    /


    For outer join
    -------------------------
    select b.deptno , b.dname
    from emp a , dept b
    where
    a.deptno(+)=b.deptno
    /

    regards
    Viraj
    ------------
    9i OCA
    Last edited by virajvk; 01-04-2003 at 07:12 AM.
    A Wise Man Knows How much he doesn't know !!!

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    IMHO, it is ridiculous to compare outer joins and equijoins - they are two totaly different things that can't be used one instead of the other!

    Saying this, it is equaly rediculous to say "Outer joins are typically expensive in terms of performance...". If your query requires an outer join then you simply have to use it, no matter how expensive it is. And when it *realy requires* an outer join then it will probably be the cheapest way to get the resultset, no matter "how expensive outer joins generaly are".

    For example, in infamous Scott schema, if you want to list all the departments and the number of their correspondent employees, nothing can beat the outer join, no matter "how expensive they generaly are":

    SELECT d.dname, COUNT(e.empno)
    FROM dept d, emp e
    WHERE d.deptno = e.empno(+);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width