Re: Need assistance in converting subqueries to joins

On 9/19/24 21:07, Siraj G wrote:
> Hello Tech gents!
> 
> I am sorry if I am asking the wrong question to this group, but wanted 
> assistance in converting a query replacing subqueries with joins.
> 
> Please find the query below (whose cost is very high):

Add the output of the EXPLAIN ANALYZE for the query.

> 
> select em_exists_id from IS_SEC_FILT WHERE (IS_SEC_FILT_GUID) NOT IN 
> (SELECT IS_OBJ_GUID FROM TMP_IS_SEC_FILT T0, IS_PROJ P0 WHERE 
> T0.IS_PROJ_GUID = P0.IS_PROJ_GUID AND P0.IS_PROJ_ID = 
> IS_SEC_FILT.IS_PROJ_ID) AND (IS_PROJ_ID) IN (SELECT IS_PROJ_ID FROM 
> IS_PROJ P0, TMP_IS_SEC_FILT T0, EM_MD R0 WHERE T0.IS_REPOSITORY_GUID = 
> R0.REP_GUID AND T0.IS_PROJ_GUID = P0.IS_PROJ_GUID AND 
> P0.IS_REPOSITORY_ID = R0.REP_ID);

For future reference formatting the query here:

https://sqlformat.darold.net/

helps get it into a form that is easier to follow:

SELECT
     em_exists_id
FROM
     IS_SEC_FILT
WHERE (IS_SEC_FILT_GUID)
NOT IN (
     SELECT
         IS_OBJ_GUID
     FROM
         TMP_IS_SEC_FILT T0,
         IS_PROJ P0
     WHERE
         T0.IS_PROJ_GUID = P0.IS_PROJ_GUID
         AND P0.IS_PROJ_ID = IS_SEC_FILT.IS_PROJ_ID)
AND (IS_PROJ_ID) IN (
     SELECT
         IS_PROJ_ID
     FROM
         IS_PROJ P0,
         TMP_IS_SEC_FILT T0,
         EM_MD R0
     WHERE
         T0.IS_REPOSITORY_GUID = R0.REP_GUID
         AND T0.IS_PROJ_GUID = P0.IS_PROJ_GUID
         AND P0.IS_REPOSITORY_ID = R0.REP_ID);


> 
> Regards
> Siraj

-- 
Adrian Klaver
adrian.klaver@aklaver.com