Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Question Unanswered: whats the use of outer join(+) in oracle

    please give me information on outer joins.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You are joking, right? Google, searching only through English and Croatian pages, returned over 23.000.000 of them. Do educate youreself; don't expect to be spoonfed all the time. Forum is here to help you about a particular problem - asking such a question is, in my opinion, impolite.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    not that anyone cares but:
    I'm not a fan of outer joins.
    I personally feel there is always a better way (performance-wise) to get the
    data than using unions or outer joins.

    My experience has been that the developers write the code against small
    amounts of data, see that it runs fast, don't account for benchmark and
    growth testing and put it into production. Then everyone wonders why a
    union-all against two 10-million row tables is hanging the production application.

    With a good developer anything is possible however.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Aug 2006
    Posts
    17
    Outerjoin is used to retreive rows from two tables having a commom column
    and also those rows from one table which do not have matching records in
    another table.
    This is done by adding null values to the corresponding table being joined.
    This is what i knew as per my knowledge as a student.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by The_Duck
    not that anyone cares but:
    I'm not a fan of outer joins.
    I personally feel there is always a better way (performance-wise) to get the
    data than using unions or outer joins.
    Such as what?

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    A scalar subquery maybe? I agree though, they are there to be used, but be aware that they limit the choices available to the optimizer.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a scalar subquery is better (performance-wise or any-other-wise) than an outer join? i doubt it

    i am still waiting for The-"with-a-good-developer-anything-is-possible"-Duck to provide illumination
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Now I have to provide examples of how I saw an outer-join and then
    proved it was a case of: "You never needed an outer-join to begin with"
    scenario?

    here is a basic example where a sub-select works better.
    1. How about queries with 10 table-joins.
    2. 3 of the tables are outer-joined?
    3. in all 3 tables, 1 column is used in the select of each
    4. in these cases I found that a simple sub-select within the select
    section of the statement worked much better (ok, assuming there is only one
    unique row for that table).

    ie:
    PHP Code:
    select
     a
    ,
     
    b,
     (
    select c from d where a zc,
     (
    select e from f where b yd,
    from 
    m
    ...   etcetc
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "worked much better" can only refer to one thing -- performance

    it certainly cannot refer to the results, because the results should be the same

    your 10-table join is interesting, but hardly enough justification to make general statements like "there is always a better way (performance-wise) to get the data than using unions or outer joins"

    if you want a general statement, how's this: the optimizer will resolve semantically equivalent queries into identical paths

    that's not always true either, but you know what? i would far more prefer to write the sql that makes it either (a) easier to write or (b) easier to understand, and let the optimizer worry about performance, than to artificially try to find a solution that avoids unions and outer joins

    of course, you're probably coming from an oracle background, and we all know how finicky oracle is about forcing you to write the most optimum sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2006
    Location
    Romania
    Posts
    8
    I think that optimizing your SQL by hand, then letting the optimizer squeeze some more out of your query is the way to go.

    I've seen cases when the optimizer did not do very much, 2 fold sub selects and such workarounds actually prevent the optimizer from doing it's job well.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by r937
    of course, you're probably coming from an oracle background, and we all know how finicky oracle is about forcing you to write the most optimum sql
    well, it IS an oracle forum and that was the first example that came to mind.


    The cases I have worked with have shown that where an outer-join was used
    that down the road it turns out it was not needed, not necessary, used
    inappropriately, or not tested thoroughly.

    The bottom line I guess is:
    Don't use outer-joins where they are not needed

    I have another real-life example I can supply, but does it matter? Someone
    will just reply "what? that never needed an outer join and doesn't even use an index". Correct! but it was an outer-join nevertheless. So maybe I am a crazy duck and just talking about bad-code and not necessarily outer-joins.

    either way someone is using them poorly and I end up finding them.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Quote Originally Posted by r937
    of course, you're probably coming from an oracle background, and we all know how finicky oracle is about forcing you to write the most optimum sql

    Great you attack one general statement by making one of your own

    Oracle's optimizer does a damn good job of handling suboptimal sql, and has got better with every version. It may not be perfect but I have yet to see an optimizer which does a better job considering the huge number of configuration and tuning options Oracle gives you.

    Alan

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, my sarcasm was obviously not as apparent as i had intended

    my apologies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    I came from an Oracle 8 environment into SQL Server 2000 a few years back. I have to say I've never ceased to be amazed at how the SQL Server optimizer has performed - to my great surprise.

    My prior (Oracle 8) environment processed 14 million payroll transactions and my new one (SQL Server 2000) is only about 3 million line items, but the Oracle optimizer just seemed to give it up after about 12 tables. I did find that subqueries (or even indexed temp tables) often improved matters, but what a mess.

    To the point: I almost always run "Query Analyzer - Show Execution Plan" on my queries and Outer Join doesn't seem to adversly affect performance to any significant level. I've always used Outer Join unless there's some Datase Integrity Constraint that guarentees it's not needed. I have some 30 table queries (normally about 10 - 15) that return results of hundreds of rows from multi-million tables in as little as 3 or 4 seconds, and the Outer Joins ensure that missing-entry exception conditions don't cause missing records.

    True: I often have to "tune my query", but I've never once found Outer Join to be a performance culprit. Am I just ignorant of how SQL Server implements it? Suffice to say, I've gotten by nicely by not "working around" the use of Outer Joins.

    If I had to sub-query each of those situations ... apart from the possible (arguably slight) performance gain ... the maintenance would be VERY cumbersome. The Query builder that I use (Enterprise manager), that offers graphic representation of the query, doesnt support subqueries too well. That tool helps me build queries in 30 minutes that used to take all day. An Outer Join is far easier to conceptualize and manage. I mark it in the "absolute necessity" category and blame the optimizer if it doesn't handle it right.
    Last edited by vich; 10-11-06 at 21:02.

Posting Permissions

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