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 Cartesian join in sql query

Discussion in 'SQL PL/SQL' started by laxman, Jul 12, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    This query may confused you little bit.but i need your attention on red marked area.

    Code (SQL):
    SELECT Request.requestID,
      TO_CHAR(Request.requestID, '000000000'),
      Request.requestName,
      Request.requestSubject,
      miniTeamName,
      TO_CHAR(Request.firstResponseDate),
      TO_CHAR(Request.closedDate),
      TO_CHAR(Request.receivedDate),
      TO_CHAR(Request.dueDate),
      DECODE(Request.responsesentind, 2, chr(14846106), ' ') ,
      Request.statusCode,
      statusDesc,
      1,
      'Request',
      Request.priorityCode,
      priorityDesc,
      Assign.firstName
      ||' '
      || Assign.lastName,
      SonarEmail.emailAddress,
      0,
      0,
      '   '
      || DECODE(Request.hasbeenread, 1, chr(14844066), ' '),
      Originator.firstName
      || ' '
      || Originator.lastName,
      rpad('',1),
      TO_CHAR(request.LASTMODDATE),
      DECODE(Request.priorityCode, 1, '11111111111111111111111111', '00000000000000000000000000'),
      isAged(Request.requestID),
      TOPICDESCRIPTION,
      SUBTOPICDESCRIPTION ,
      NVL(Request.Ratings,0),
      '  '
      || DECODE(Request.ISconverted_request, 1, chr(14844064), ' '),
      NVL(Request.technicalratings,0) technicalratings,
      NVL(Request.nontechnicalratings,0) nontechnicalratings ,
      NVL(PRODUCTNAME,' ') productName,
      NVL(PRODUCTCOMPONENT, ' ') productComponent,
      NVL(PRODUCTISSUE, ' ') productIssue,
      NVL(PRODUCTSUBISSUE, ' ') productSubIssue,
      Requestlabel.labelID
    FROM Request Request,
      Person Assign,
      SonarEmail,
      MiniTeam,
      StatusType,
      PriorityType,
      Person Originator,
      Topic,
      Subtopic ,
      RequestLabel RequestLabel
    WHERE EXISTS
      (SELECT childMiniTeamID
      FROM MiniTeamTree b
      WHERE b.childMiniTeamID = request.miniTeamID
      AND b.PARENTMINITEAMID  = 17969
      AND b.LEVELFROMPARENT   = 0
      )
    AND EXISTS
      (SELECT 1
      FROM Topic,
        SubTopic
      WHERE SubTopic.topicID       =Topic.topicID
      AND SubTopic.validityTypeCode<2
      AND SubTopic.subtopicID      =request.subtopicID
      AND LOWER(topicDescription) LIKE 'topic%'
      )
    AND Request.requestTypeCode      = 1
    AND StatusType.requestTypeCode   = Request.requestTypeCode
    AND MiniTeam.miniTeamID          = Request.miniTeamID
    AND StatusType.statusCode        = Request.statusCode
    AND PriorityType.priorityCode    = Request.priorityCode
    AND Assign.personID(+)           = Request.assigned_PersonID
    AND SonarEmail.emailAddressID (+)=Request.originatorEmailAddressID
    AND SonarEmail.PersonID          = Originator.personID
    AND Subtopic.subtopicID(+)       = Request.subtopicID
    AND Topic.topicID(+)             =Subtopic.topicID
    AND request.requestID            = RequestLabel.RequestID(+)
    AND RequestLabel.personID (+)    = SonarSession.spersonID
    ORDER BY Request.requestID
    current functionality
    -----------------------
    with this query without modification if i am executing the query i am getting unique row let say the output is like this below

    Note: since i am concern with the requestid I am showing you only output of one attribute but actual query may provide output of multiple attributes.

    output
    -------

    Requestid
    ------------
    11111
    22222
    33333
    44444

    proposed functionality
    ------------------------

    i need the output of each requestid which are also existing in requestassignmenthistory table
    the output could be something like this

    output
    -------
    11111
    11111
    11111
    22222
    22222

    **** value of the requestid is also existing in requestassignmenthistory table in this format.

    sql> select * from requestassigmenthistory;
    requestid topicid subtopicid
    ---------- ------- -----------
    11111 3456 4567
    11111 3456 2367
    11111 3456 2578
    22222 1678 4598
    22222 3467 7689

    sql> select * from topic;
    topicid topicdesc
    ------- -----------
    3456 topic
    1678 topic1
    3467 topic2

    sql> select * from subtopic
    subtopicid subtopicdesc
    ----------- ---------------
    4567 subtopic
    2367 subtopic1
    2578 subtopic2

    ***To fulfill the requirement i have included one subquery in the existing query mentioned below.The output i am getting but it is performing Cartesian join and here i want to avoid Cartesian join.
    I need ur suggestion if in my new added subquery i am doing any mistakes.

    AND request.requestid IN
    (SELECT requestid
    FROM requestassignmenthistory rh,
    topic t
    WHERE lower(t.topicdescription) LIKE lower('topic')
    AND rh.topicid=t.topicid



    Code (SQL):
    SELECT [COLOR="red"]Request.requestID[/COLOR],
      TO_CHAR(Request.requestID, '000000000'),
      Request.requestName,
      Request.requestSubject,
      miniTeamName,
      TO_CHAR(Request.firstResponseDate),
      TO_CHAR(Request.closedDate),
      TO_CHAR(Request.receivedDate),
      TO_CHAR(Request.dueDate),
      DECODE(Request.responsesentind, 2, chr(14846106), ' ') ,
      Request.statusCode,
      statusDesc,
      1,
      'Request',
      Request.priorityCode,
      priorityDesc,
      Assign.firstName
      ||' '
      || Assign.lastName,
      SonarEmail.emailAddress,
      0,
      0,
      '   '
      || DECODE(Request.hasbeenread, 1, chr(14844066), ' '),
      Originator.firstName
      || ' '
      || Originator.lastName,
      rpad('',1),
      TO_CHAR(request.LASTMODDATE),
      DECODE(Request.priorityCode, 1, '11111111111111111111111111', '00000000000000000000000000'),
      isAged(Request.requestID),
      [COLOR="red"]TOPICDESC[/COLOR],
      [COLOR="red"]SUBTOPICDESC[/COLOR] ,
      NVL(Request.Ratings,0),
      '  '
      || DECODE(Request.ISconverted_request, 1, chr(14844064), ' '),
      NVL(Request.technicalratings,0) technicalratings,
      NVL(Request.nontechnicalratings,0) nontechnicalratings ,
      NVL(PRODUCTNAME,' ') productName,
      NVL(PRODUCTCOMPONENT, ' ') productComponent,
      NVL(PRODUCTISSUE, ' ') productIssue,
      NVL(PRODUCTSUBISSUE, ' ') productSubIssue,
      Requestlabel.labelID
    FROM Request Request,
      Person Assign,
      SonarEmail,
      MiniTeam,
      StatusType,
      PriorityType,
      Person Originator,
      [COLOR="red"]Topic[/COLOR],
      [COLOR="red"]Subtopic[/COLOR] ,
      RequestLabel RequestLabel
    WHERE EXISTS
      (SELECT childMiniTeamID
      FROM MiniTeamTree b
      WHERE b.childMiniTeamID = request.miniTeamID
      AND b.PARENTMINITEAMID  = 17969
      AND b.LEVELFROMPARENT   = 0
      )
    AND EXISTS
      (SELECT 1
      FROM Topic,
        SubTopic
      WHERE SubTopic.topicID       =Topic.topicID
      AND SubTopic.validityTypeCode<2
      AND SubTopic.subtopicID      =request.subtopicID
      AND LOWER(topicDescription) LIKE 'topic%'
      )
    [COLOR="red"]AND request.requestid IN
      (SELECT requestid
      FROM requestassignmenthistory rh,
        topic t
      WHERE LOWER(t.topicdescription) LIKE LOWER('topic')
      AND rh.topicid=t.topicid
      )[/COLOR]
    AND Request.requestTypeCode      = 1
    AND StatusType.requestTypeCode   = Request.requestTypeCode
    AND MiniTeam.miniTeamID          = Request.miniTeamID
    AND StatusType.statusCode        = Request.statusCode
    AND PriorityType.priorityCode    = Request.priorityCode
    AND Assign.personID(+)           = Request.assigned_PersonID
    AND SonarEmail.emailAddressID (+)=Request.originatorEmailAddressID
    AND SonarEmail.PersonID          = Originator.personID
    AND Subtopic.subtopicID(+)       = Request.subtopicID
    AND Topic.topicID(+)             =Subtopic.topicID
    AND request.requestID            = RequestLabel.RequestID(+)
    AND RequestLabel.personID (+)    = SonarSession.spersonID
    ORDER BY Request.requestID
    Thanks n regards
    Laxman
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Instead of sub query add those two tables to main query..

    Hope this will help you.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We need to see the plan before we can really suggest anything.

    Please post the plan this query generates.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    This is the plan which I guess...
    Because i dont have these tables. so according to the query i understand and suggested below query....

    SELECT Request.requestID,
    TO_CHAR(Request.requestID, '000000000'),
    Request.requestName,
    Request.requestSubject,
    miniTeamName,
    TO_CHAR(Request.firstResponseDate),
    TO_CHAR(Request.closedDate),
    TO_CHAR(Request.receivedDate),
    TO_CHAR(Request.dueDate),
    DECODE(Request.responsesentind, 2, chr(14846106), ' ') ,
    Request.statusCode,
    statusDesc,
    1,
    'Request',
    Request.priorityCode,
    priorityDesc,
    Assign.firstName
    ||' '
    || Assign.lastName,
    SonarEmail.emailAddress,
    0,
    0,
    ' '
    || DECODE(Request.hasbeenread, 1, chr(14844066), ' '),
    Originator.firstName
    || ' '
    || Originator.lastName,
    rpad('',1),
    TO_CHAR(request.LASTMODDATE),
    DECODE(Request.priorityCode, 1, '11111111111111111111111111', '00000000000000000000000000'),
    isAged(Request.requestID),
    TOPICDESC,
    SUBTOPICDESC ,
    NVL(Request.Ratings,0),
    ' '
    || DECODE(Request.ISconverted_request, 1, chr(14844064), ' '),
    NVL(Request.technicalratings,0) technicalratings,
    NVL(Request.nontechnicalratings,0) nontechnicalratings ,
    NVL(PRODUCTNAME,' ') productName,
    NVL(PRODUCTCOMPONENT, ' ') productComponent,
    NVL(PRODUCTISSUE, ' ') productIssue,
    NVL(PRODUCTSUBISSUE, ' ') productSubIssue,
    Requestlabel.labelID
    FROM Request Request,
    Person Assign,
    SonarEmail,
    MiniTeam,
    StatusType,
    PriorityType,
    Person Originator,
    Topic,
    Subtopic ,
    RequestLabel RequestLabel,
    requestassignmenthistory rh,
    topic t

    WHERE EXISTS
    (SELECT childMiniTeamID
    FROM MiniTeamTree b
    WHERE b.childMiniTeamID = request.miniTeamID
    AND b.PARENTMINITEAMID = 17969
    AND b.LEVELFROMPARENT = 0
    )
    AND EXISTS
    (SELECT 1
    FROM Topic,
    SubTopic
    WHERE SubTopic.topicID =Topic.topicID
    AND SubTopic.validityTypeCode<2
    AND SubTopic.subtopicID =request.subtopicID
    AND LOWER(topicDescription) LIKE 'topic%'
    )

    AND request.requestid =rh.requestid
    And t.topicdescription LIKE LOWER('topic')
    AND rh.topicid=t.topicid


    AND Request.requestTypeCode = 1
    AND StatusType.requestTypeCode = Request.requestTypeCode
    AND MiniTeam.miniTeamID = Request.miniTeamID
    AND StatusType.statusCode = Request.statusCode
    AND PriorityType.priorityCode = Request.priorityCode
    AND Assign.personID(+) = Request.assigned_PersonID
    AND SonarEmail.emailAddressID (+)=Request.originatorEmailAddressID
    AND SonarEmail.PersonID = Originator.personID
    AND Subtopic.subtopicID(+) = Request.subtopicID
    AND Topic.topicID(+) =Subtopic.topicID
    AND request.requestID = RequestLabel.RequestID(+)
    AND RequestLabel.personID (+) = SonarSession.spersonID
    ORDER BY Request.requestID
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is no execution plan posted; what IS that plan? And are you now saying that the query you posted is NOT the query you're having issues with? If it is the 'problem' statement it will have a sql_id value you can use to return the execution plan.

    Provide the execution plan (you DO have it because you are reporting a CARTESIAN JOIN and you wouldn't know that information if the plan wasn't available).