Query tuning - please help!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query tuning - please help!!

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    Query tuning - please help!!

    How do I get rid of the sort merge join? The explain plan and the query are given below:


    Code:
     ----------------------------------------------------------------------------------------------------
    | Id  | Operation                           |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                    |     1 |   575 |    19 |       |       |
    |   1 |  SORT UNIQUE                        |                    |     1 |   575 |    19 |       |       |
    |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| ISSUE              |     1 |    29 |     2 | ROWID | ROW L |
    |   3 |    NESTED LOOPS                     |                    |     1 |   575 |    15 |       |       |
    |   4 |     NESTED LOOPS                    |                    |     4 |  2184 |    13 |       |       |
    |   5 |      NESTED LOOPS                   |                    |     7 |  2457 |    11 |       |       |
    |   6 |       MERGE JOIN CARTESIAN          |                    |  6406 |  1707K|    11 |       |       |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   7 |        PARTITION HASH ALL           |                    |       |       |       |     1 |     8 |
    |*  8 |         INDEX FAST FULL SCAN        | SYS_IOT_TOP_30857  |     7 |  1365 |     2 |     1 |     8 |
    |   9 |        BUFFER SORT                  |                    |   980 | 76440 |     9 |       |       |
    |  10 |         PARTITION HASH ALL          |                    |       |       |       |     1 |    12 |
    |* 11 |          INDEX FAST FULL SCAN       | SYS_IOT_TOP_30830  |   980 | 76440 |     2 |     1 |    12 |
    |  12 |       PARTITION HASH ITERATOR       |                    |       |       |       |   KEY |   KEY |
    |* 13 |        INDEX UNIQUE SCAN            | SYS_IOT_TOP_30830  |     1 |    78 |       |   KEY |   KEY |
    |  14 |      PARTITION HASH ITERATOR        |                    |       |       |       |   KEY |   KEY |
    |* 15 |       INDEX RANGE SCAN              | SYS_IOT_TOP_30857  |     1 |   195 |     1 |   KEY |   KEY |
    |* 16 |     INDEX RANGE SCAN                | XIF9ISSUE1         |   115 |       |     1 |       |       |
    ----------------------------------------------------------------------------------------------------
    Last edited by newbie5; 03-12-2004 at 08:23 PM.

  2. #2
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    This is the query

    Code:
    SELECT DISTINCT 
    REL.USERNAME AS "USERNAME", 
    REL.RELATIONSHIP AS "RELATIONSHIP" 
    FROM DOCUSRRELATION REL 
    INNER JOIN ISSUE ISS ON ISS.ISSUEID = REL.DOCUMENTID 
    AND ISS.STATUSID != 2 AND ISS.MARKEDFORDELETION = 'N' 
    WHERE REL.RELATIONSHIP = 'IssueCoordinator' AND REL.DOCUMENTID IN ( 
                         SELECT DISTINCT SECURE.ANCESTORDOCUMENTID 
                         FROM DOCUMENTHIERARCHY SECURE 
                         WHERE SECURE.DESCENDANTDOCID IN 
                            (SELECT DISTINCT ASTR.DESCENDANTDOCID 
                                                AS DOCUMENTID 
                               FROM DOCUSRRELATION DUR 
                               INNER JOIN DOCUMENTHIERARCHY ASTR 
                               ON DUR.DOCUMENTID =       
                               ASTR.ANCESTORDOCUMENTID 
                               AND ASTR.DESCENDANTSRCTABLE IN
                                    ('Issue', 'ActionPlan') 
                                    WHERE DUR.USERNAME IN 
                           ('greg.stier/OAI','[Paisley Java Dev]') 
    AND DUR.RELATIONSHIP IN ('BusinessRiskAreaOwners', 'IssueAdditionalReaders', 
    'BusinessRiskAreaVicePresidents', 'ActionPlanSendOnClose', 
    'Account_2Owners', 'Account_1Owners', 'BusinessRiskAreaSponsors', 
    'ControlTestOwner', 'IssueCoordinator', 
    'ActionPlanAdditionalReaders', 'ActionPlanCoordinators', 
    'IssueSendOnClose', 'ActionPlanReviewers', 
    'BusinessRiskAreaOtherReaders')) 
    AND SECURE.ANCESTORSRCTABLE = 'Issue')
    Last edited by newbie5; 03-12-2004 at 08:22 PM.

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Start with rewriting IN clauses of the sub-query.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Or maybe an inline view...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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