Have merge Cartesian Join with high cost

Question

We were querying the DB to populate some logged tickets, however the query formed causing the above issue and is communicated by our performance team.

Here I am into Java programming and I don't have much idea on these joins. How can I the re-frame below piece of query to avoid the merge Cartesian Join with high cost?

FROM
    SERVICE_REQ SR,
    SR_COBRAND_DATA SR_COB_DATA,
    REPOSITORY rep,
    SR_ASSIGNEE_INFO ASSIGNEE_INFO 
WHERE
    SR.SR_COBRAND_ID=rep.COBRAND_ID 
    AND SR.SERVICE_REQ_ID=SR_COB_DATA.SERVICE_REQ_ID (+) 
    AND SR.SERVICE_REQ_ID = ASSIGNEE_INFO.SERVICE_REQ_ID (+) 
    AND SR.SR_COBRAND_ID = 99

Alas, there is simply not enough information for us to provide an answer. Please read [this thorough post on asking Oracle tuning questions (link)](https://stackoverflow.com/a/34975420/146325). At the very least you will learn to ask a question which we might be able to answer. But you may also gain an insight into Oracle performance which could help you tune your queries yourself. — APC, Feb 25 '19 at 08:11

score 0 · Answer 1 · edited Feb 10 '23 at 21:25

0

Just a suggestion: you should not use old implicit join syntax but join explicit join syntax:

SELECT * 
FROM  SERVICE_REQ SR 
LEFT JOIN  SR_COBRAND_DATA SR_COB_DATA ON SR.SERVICE_REQ_ID=SR_COB_DATA.SERVICE_REQ_ID
INNER JOIN REPOSITORY rep  ON SR.SR_COBRAND_ID=rep.COBRAND_ID 
LEFT JOIN SR_ASSIGNEE_INFO ASSIGNEE_INFO  ON SR.SERVICE_REQ_ID = ASSIGNEE_INFO.SERVICE_REQ_ID
WHERE SR.SR_COBRAND_ID = 99

Anyway, based on this condition you have not a Cartesian product between the table but a left join for SERVICE_REQ with SR_COBRAND_DATA and SR_ASSIGNEE_INFO reduce by inner join with REPOSITORY.

Perhaps to explain you goal you should add proper sample data, the expected result, and your actual result.

edited Feb 10 '23 at 21:25

halfer

19,824
17
99
186

answered Feb 25 '19 at 07:01

ScaisEdge

131,976
10
91
107

2

This is not an answer to the question. No matter how irritating you find the ANSI 89 syntax, converting the statement to use explicit joins will not change the performance of the query. @MarmiteBomber posted [a self-answered question on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325) a while back. I comment and refer OPs to it. It's easier than going through all the things we need in an Oracle tuning question. – APC Feb 25 '19 at 08:15

score 0 · Answer 2 · answered Feb 25 '19 at 19:26

0

Create a composite index on columns SR_COBRAND_ID and SERVICE_REQ_ID of table SERVICE_REQ

-- Create Index [indexname] on SERVICE_REQ (SR_COBRAND_ID , SERVICE_REQ_ID);

answered Feb 25 '19 at 19:26

Ahmed Mustafa

19
6

Have merge Cartesian Join with high cost

2 Answers2