public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Siraj G <[email protected]>
To: [email protected]
Subject: Re: Need assistance in converting subqueries to joins
Date: Thu, 19 Sep 2024 21:19:16 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAC5iy60YRzqRSyC=hAZneURdCTAsRPH313jJzK9gF6-nkvwGPA@mail.gmail.com>
References: <CAC5iy60YRzqRSyC=hAZneURdCTAsRPH313jJzK9gF6-nkvwGPA@mail.gmail.com>

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
[email protected]

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Need assistance in converting subqueries to joins
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox