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(dot)klaver(at)aklaver(dot)com