-
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.
-
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.
-
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
-
Or maybe an inline view...
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|