时间戳

首页 > 解决方案 > 以更有效的方式表达 oracle 查询

sql - 以更有效的方式表达 oracle 查询

问题描述

我有以下 oracle 查询，它正在获取结果并以完美的方式带来结果，但请告知我可以以更有效的方式编写以下查询

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

标签： sqloracle

解决方案

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.

推荐阅读