Have merge Cartesian Join with high Cost

Question

Have merge Cartesian Join with high Cost.

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. Can someone help me out with how can 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

ScaisEdge · Answer 1 · 2019-02-25T08:15:54.163

0

Just a suggestion

you should not use old implicit join sintax but join explicict join sintax

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

could be that for explain you goal you should add proper data sample the expected result and you actual result

edited Feb 25 '19 at 08:15

answered Feb 25 '19 at 07:01

ScaisEdge

129,293
10
87
97

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