How to rewrite query NOT IN with join condition

Below query having high cost, dur to NOT IN (sub-query)

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.

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