1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to avoid multiple joins in the query

Discussion in 'SQL PL/SQL' started by Ramayan, Jan 17, 2013.

  1. Ramayan

    Ramayan Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    India
    Hi Forum,

    i have this piece of code that is creating a performance issue due to multiple joins ( left outer join)
    i have
    Code (SQL):
     LEFT OUTER JOIN T1
                       ON (T2.aftercallworktime >= T1.startduration
                       AND T2.aftercallworktime <= T1.endduration)
                    LEFT OUTER JOIN T1
                       ON (T3.answeredholdtime >= T1.startduration
                       AND T3.answeredholdtime <= T1.endduration).............
                        LEFT OUTER JOIN T1
                         ...................
                          ...................
                          ....................
    its life 45 plus line and there are about more then 20 plus Left Outer joins

    One more thing
    is there any oracle function to take care of there conditions for every single check ( <= and >=)


    looking forward for the positive response.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have an execution plan for this query? If so what does it show? Providing the small 'snippet' of code doesn't help us much in determining how you can 'fix' this apparently problematic query. Please provide an example which closely resembles the actual code (presuming you cannot provide the actual statement) plus DDL to create the necessary objects so we can test possible solutions and offer you something of value.
     
  3. ocpgizmo

    ocpgizmo Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Even worse than the fact that there are so many, and that they are LEFT joins is that they are not equijoins. Would need the entire piece of SQL to even have a chance at coming up with a better option.
     
  4. Ramayan

    Ramayan Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    India
    Thanks for the response guys !!