Query tuning - please help!!

[newbie5]

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

 ----------------------------------------------------------------------------------------------------
| 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 |       |       |
----------------------------------------------------------------------------------------------------

[newbie5]

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')

[kris109]

Start with rewriting IN clauses of the sub-query.

[marist89]

Or maybe an inline view...