Re: Need assistance in converting subqueries to joins

Hello Adrian!

Please find below the query in the format and its execution plan:

SELECT
em_exists_idFROM
IS_SEC_FILTWHERE (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);

Query plan:

'-> Aggregate: count(0) (cost=2284.32 rows=1988) (actual
time=22602.583..22602.584 rows=1 loops=1)\n
-> Remove duplicate (P0, IS_SEC_FILT) rows using temporary table
(weedout) (cost=2085.53 rows=1988) (actual time=0.321..22600.652
rows=10298 loops=1)\n -> Filter:
<in_optimizer>(IS_SEC_FILT.IS_SEC_FILT_GUID,<exists>(select #2) is
false) (cost=2085.53 rows=1988) (actual time=0.315..22433.412
rows=514900 loops=1)\n
-> Inner hash join (IS_SEC_FILT.IS_PROJ_ID = P0.IS_PROJ_ID)
(cost=2085.53 rows=1988) (actual time=0.188..96.362 rows=517350
loops=1)\n
-> Index scan on IS_SEC_FILT using IS_SEC_FILT_PK (cost=28.84
rows=19879) (actual time=0.019..7.386 rows=20086 loops=1)\n
-> Hash\n
-> Nested loop inner join (cost=8.05 rows=1) (actual
time=0.064..0.132 rows=50 loops=1)\n
-> Inner hash join (T0.IS_REPOSITORY_GUID = R0.REP_GUID) (cost=1.70
rows=1) (actual time=0.047..0.094 rows=50 loops=1)\n
-> Filter: (T0.IS_PROJ_GUID is not null) (cost=0.38 rows=5) (actual
time=0.010..0.041 rows=50 loops=1)\n
-> Table scan on T0 (cost=0.38 rows=50) (actual time=0.010..0.037
rows=50 loops=1)\n
-> Hash\n
-> Filter: (R0.REP_ID is not null) (cost=0.45 rows=2) (actual
time=0.022..0.025 rows=2 loops=1)\n
-> Table scan on R0 (cost=0.45 rows=2) (actual time=0.021..0.023
rows=2 loops=1)\n
-> Filter: (P0.IS_REPOSITORY_ID = R0.REP_ID) (cost=0.63 rows=1)
(actual time=0.001..0.001 rows=1 loops=50)\n
-> Single-row index lookup on P0 using IS_PROJ_PK
(IS_PROJ_GUID=T0.IS_PROJ_GUID, IS_REPOSITORY_ID=R0.REP_ID) (cost=0.63
rows=1) (actual time=0.000..0.000 rows=1 loops=50)\n
-> Select #2 (subquery in condition; dependent)\n
-> Limit: 1 row(s) (cost=5.98 rows=1) (actual time=0.043..0.043
rows=0 loops=517350)\n
-> Filter: <is_not_null_test>(T0.IS_OBJ_GUID) (cost=5.98 rows=1)
(actual time=0.043..0.043 rows=0 loops=517350)\n
-> Filter: ((<cache>(IS_SEC_FILT.IS_SEC_FILT_GUID) = T0.IS_OBJ_GUID)
or (T0.IS_OBJ_GUID is null)) (cost=5.98 rows=1) (actual
time=0.042..0.042 rows=0 loops=517350)\n
-> Inner hash join (T0.IS_PROJ_GUID = P0.IS_PROJ_GUID) (cost=5.98
rows=1) (actual time=0.004..0.038 rows=50 loops=517350)\n
-> Table scan on T0 (cost=0.35 rows=50) (actual
time=0.001..0.022 rows=50 loops=517350)\n
-> Hash\n -> Single-row index
lookup on P0 using PRIMARY (IS_PROJ_ID=IS_SEC_FILT.IS_PROJ_ID)
(cost=0.72 rows=1) (actual time=0.001..0.001 rows=1 loops=517350)\n'

On Fri, Sep 20, 2024 at 9:49 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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