expressing oracle query in more efficient manner

Question

I have the below oracle query which is fetching the results and bringing the results in a perfect manner , but please advise can i write the below query in more efficient manner

SELECT DISTINCT PSNETWORKID
FROM JGH
INNER JOIN IDF
ON IDF.PSIDFFIID         = JGH.PSNETWORKID
WHERE (JGH.zzACQUIRERID IN
  (SELECT DISTINCT pstdftermid FROM YUI WHERE pstdftermownid =111111
  )
OR zzACQUIRERID = 111111);

The query only describes which data should be selected; the choosen **execution plan** is relevant for the *efficiency* of the execution. Pls, post the [execution plan](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) and **decribe the problem** you encouter. *If there is no problem, do not try to create one;)* — Marmite Bomber, Jun 13 '18 at 15:59

score 0 · Answer 1 · answered Jun 13 '18 at 15:27

This appears to be your query:

select distinct PSNETWORKID 
from JGH INNER JOIN
     IDF 
     on IDF.PSIDFFIID = JGH.PSNETWORKID 
where JGH.zzACQUIRERID in (select pstdftermid 
                           from YUI
                           where pstdftermownid = 111111
                          ) or
      zzACQUIRERID = 111111;

First, select distinct is usually a bad idea. Let me assume it is unique in one of the tables -- say JFH. Then, I would approach this as:

select jgh.PSNETWORKID 
from JGH 
where exists (select 1
              from IDF
              where IDF.PSIDFFIID = JGH.PSNETWORKID
             ) and
      (exists (select 1
               from YUI
               where JGH.zzACQUIRERID = YUI.pstdftermid and YUI.pstdftermownid = 111111
             ) or
       jgh.zzACQUIRERID = 111111
      );

I would then recommend indexes on IDF(PSIDFFIID) and YUI(pstdftermid, pstdftermownid).

Error_2646 · Answer 2 · 2018-06-13T18:05:35.420

By "efficient" do you mean succinct or quicker run time? Generally you want to use EXIST rather than IN.

SELECT distinct PSNETWORKID  
  FROM JGH 
 INNER 
  JOIN IDF 
    ON IDF.PSIDFFIID= JGH.PSNETWORKID 
 WHERE EXISTS  
          ( SELECT 1 
              FROM (SELECT pstdftermid conditionField
                      FROM YUI 
                     UNION ALL
                    SELECT 111111 AS conditionField
                      FROM dual
                   ) TMP
             WHERE JGH.zzACQUIRERID = TMP.conditionField
          );

score 0 · Answer 3 · answered Jun 13 '18 at 17:44

This is tested on Oracle 11.2.0.2.0. Note that your version is relevant here, because some very good advise for older version could be irrelevant for newer version.

This is the execution plan I got with your query and generated data

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    39 |       |   437   (1)| 00:00:06 |
|   1 |  HASH UNIQUE         |             |     1 |    39 |       |   437   (1)| 00:00:06 |
|*  2 |   FILTER             |             |       |       |       |            |          |
|*  3 |    HASH JOIN         |             |   108K|  4142K|  2656K|   436   (1)| 00:00:06 |
|   4 |     TABLE ACCESS FULL| IDF         |   108K|  1380K|       |    46   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| JGH         |   109K|  2786K|       |    62   (2)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN  | PSTDFTERMID |   725 | 18850 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("JGH"."ZZACQUIRERID"=111111 OR  EXISTS (SELECT 0 FROM "YUI" "YUI" 
              WHERE "PSTDFTERMOWNID"=111111 AND "PSTDFTERMID"=:B1))
   3 - access("IDF"."PSIDFFIID"="JGH"."PSNETWORKID")
   6 - access("PSTDFTERMID"=:B1 AND "PSTDFTERMOWNID"=111111)

So, one possible advise to make your query more "effective" could be re-order the OR predicate and put the subquery on the second place to avoid execution the subquery if the predicate zzACQUIRERID = 111111 is true. But if you see the the filter on line 2 above, this is done for you by Oracle.

Next advice is to use EXISTS instead of the the IN operator. The same is true if you look in the second part of the filter - the query is rewriten and uses EXISTS.

So the most important part is (and I guess this is relevant to your query if it never ends - which you don't mentioned) to have the index on YUI(pstdftermid, pstdftermownid). This gives you the index access in the line 6.

If you see in this place something like

|*  8 |      TABLE ACCESS FULL | YUI       |  10000 |    100 |  10000 |00:00:01.82 |     131K|       |       |          |

   8 - filter(("PSTDFTERMID"=:B1 AND "PSTDFTERMOWNID"=111111))

this would lead to very ineffective execution, making one full table scan for each PSTDFTERMID key.

So my advice is in the first step check the excution plan and be sure there is the mentioned index.

Rewrite the query only if you want to force Oracle to take a different execution plan, not based on the best practices from the past versions.

expressing oracle query in more efficient manner

3 Answers3