Below query having high cost, dur to NOT IN (sub-query)
x
select
clm.column1,
column2,
ins.column3,
dia.column4,
clm.column5
From
table1 clm
inner join table2 ins on clm.key = ins.key
left outer join table3 SFX ON
clm.number = SFX.number
and
id in (
select
max(id)
from
table3
group by number
)
AND
clm.column1 NOT IN (
sELECT
column1
FROM
prod
)
and
TO_CHAR( SFX.app_dt, 'YYYYMMDD' ) = to_char( '21-06-2020', 'YYYYMMDD' )
left outer join (
SELECT
column1,
RTRIM(
XMLAGG( XMLELEMENT( E,MD.process, ',' ).EXTRACT( '//text()' ) ORDER BY column1 ).GetClobVal(), ','
) column4
FROM
table4 D
INNER JOIN table5 MD ON MD.key = D.id
GROUP BY
column1
) dia on clm.column1 = dia.column1
where
clm.column1 not in ( select column1 from prod );
I dont have idea how to rewrite the AND clm.column1 NOT IN(sELECT column1 FROM prod)
with join condition.
If have any idea, please let me know.
Advertisement
Answer
There are multiple issues in your query. I tried to resolve them as much as possible in following code:
select clm.column1, column2, ins.column3, dia.column4, clm.column5
From table1 clm
inner join table2 ins
on clm.key = ins.key
left outer join table3 SFX
ON clm.number = SFX.number
-- DON'T USE SUB-QUERIES IN JOIN
-- ADDED IT IN THE WHERE CLAUSE
--id in ( select max(id) from table3 group by number )
-- WHY THIS SUBQUERY IS HERE. IT CAN BE IN LEFT JOIN OR WHERE
-- ADDED IN LEFT JOIN
--AND clm.column1 NOT IN ( sELECT column1 FROM prod )
--
-- IF THERE IS INDEX ON SFX.app_dt THEN USE THE >= AND < AS FOLLOWS
-- and TO_CHAR( SFX.app_dt, 'YYYYMMDD' ) = to_char( '21-06-2020', 'YYYYMMDD' )
AND SFX.app_dt >= DATE '2020-06-21' AND SFX.app_dt < DATE '2020-06-22'
left outer join (
SELECT column1,
RTRIM(
XMLAGG( XMLELEMENT( E,MD.process, ',' ).EXTRACT( '//text()' )
ORDER BY column1 ).GetClobVal(), ','
) column4
FROM table4 D INNER JOIN table5 MD ON MD.key = D.id
GROUP BY column1
) dia on clm.column1 = dia.column1
-- ADDED FOLLOWING LEFT JOIN
LEFT JOIN PROD P ON P.COLUMN1 = CLM.COLUMN1
-- ADDED ENTIRE NEW WHERE CLAUSE
where P.COLUMN1 IS NULL
AND (SFX.ID IS NULL OR SFX.id in ( select max(id) from table3 group by number) );