SQL优化之Exists

最新推荐文章于 2024-09-19 22:25:59 发布

出埃及

最新推荐文章于 2024-09-19 22:25:59 发布

阅读量640

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle Sql Tuning 文章标签： sql 优化 access filter loops nested

本文链接：https://blog.youkuaiyun.com/Coast_lichao/article/details/7574389

Oracle Sql Tuning 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个具体的SQL查询案例，通过对比两种不同的查询方式及其执行计划，展示了如何通过修改查询语句来减少数据库操作的成本，从而达到提高查询效率的目的。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> select bd_accsubj.pk_accsubj,
  2         bd_accsubj.subjcode,
  3         bd_accsubj.subjname,
  4         bd_accsubj.endflag
  5    from ufnc5610.bd_accsubj
  6   where bd_accsubj.pk_glorgbook = '0001AI1000000003PENU'
  7     and sealflag is null
  9     and bd_accsubj.endflag = 'Y'
10       and exists
11   (select subj1.Pk_accsubj
12            from ufnc5610.bd_accsubj subj1, ufnc5610.gl_verifyObj
13           where gl_verifyObj.Pk_accsubj = subj1.Pk_accsubj
14             and (gl_verifyObj.pk_glorgbook = '0001AI1000000003PENU' and
15                 bd_accsubj.subjcode like subj1.subjcode || '%' and
16                 gl_verifyObj.userFlag = 'Y'
17                 )
18                 );
 
SQL> /
 
已选择86行。
 
执行计划
----------------------------------------------------------
Plan hash value: 3518202699
 
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    68 |   660   (0)| 00:00:08 |
|*  1 |  FILTER                       |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID | BD_ACCSUBJ     |   591 | 40188 |    69   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | I_BD_ACCSUBJ_3 |   746 |       |     3   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                |                |     1 |    83 |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| GL_VERIFYOBJ   |     8 |   424 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | I_GL_VERIFYOBJ |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| BD_ACCSUBJ     |     1 |    30 |     1   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN         | PK_BD_ACCSUBJ  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "UFNC5610"."GL_VERIFYOBJ"
              "GL_VERIFYOBJ","UFNC5610"."BD_ACCSUBJ" "SUBJ1" WHERE
              "GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ" AND :B1 LIKE "SUBJ1"."SUBJCODE"||'%'
              AND "GL_VERIFYOBJ"."PK_GLORGBOOK"='0001AI1000000003PENU' AND
              "GL_VERIFYOBJ"."USERFLAG"='Y'))
   2 - filter("SEALFLAG" IS NULL AND "BD_ACCSUBJ"."ENDFLAG"='Y')
   3 - access("BD_ACCSUBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
   5 - filter("GL_VERIFYOBJ"."USERFLAG"='Y')
   6 - access("GL_VERIFYOBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
   7 - filter(:B1 LIKE "SUBJ1"."SUBJCODE"||'%')
   8 - access("GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ")
 
 
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16806  consistent gets
          0  physical reads
          0  redo size
       5242  bytes sent via SQL*Net to client
        278  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         86  rows processed
 
like 连接无法被unnest，修改为表连接的方式：
SQL> select bd_accsubj.pk_accsubj,
  2         bd_accsubj.subjcode,
  3         bd_accsubj.subjname,
  4         bd_accsubj.endflag
  5    from ufnc5610.bd_accsubj  , (select distinct subj1.subjcode
  6            from ufnc5610.bd_accsubj subj1, ufnc5610.gl_verifyObj
  7           where gl_verifyObj.Pk_accsubj = subj1.Pk_accsubj
  8             and (gl_verifyObj.pk_glorgbook = '0001AI1000000003PENU' and
  9                  gl_verifyObj.userFlag = 'Y'
10                 )
11                 ) c
12   where bd_accsubj.pk_glorgbook = '0001AI1000000003PENU'
13     and sealflag is null
14     and bd_accsubj.endflag = 'Y'
15   and  bd_accsubj.subjcode like c.subjcode || '%';


已选择86行。

执行计划
----------------------------------------------------------
Plan hash value: 2564690268


---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |   236 | 21240 |    63   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | BD_ACCSUBJ     |    30 |  2040 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                   |                |   236 | 21240 |    63   (2)| 00:00:01 |
|   3 |    VIEW                          |                |     8 |   176 |     8  (13)| 00:00:01 |
|   4 |     SORT UNIQUE                  |                |     8 |   664 |     8  (13)| 00:00:01 |
|   5 |      NESTED LOOPS                |                |     8 |   664 |     7   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| GL_VERIFYOBJ   |     8 |   424 |     1   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | I_GL_VERIFYOBJ |     1 |       |     1   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID| BD_ACCSUBJ     |     1 |    30 |     1   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | PK_BD_ACCSUBJ  |     1 |       |     1   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN              | I_BD_ACCSUBJ_2 |     7 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


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


   1 - filter("SEALFLAG" IS NULL AND "BD_ACCSUBJ"."ENDFLAG"='Y')
   6 - filter("GL_VERIFYOBJ"."USERFLAG"='Y')
   7 - access("GL_VERIFYOBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
   9 - access("GL_VERIFYOBJ"."PK_ACCSUBJ"="SUBJ1"."PK_ACCSUBJ")
  10 - access("BD_ACCSUBJ"."SUBJCODE" LIKE "C"."SUBJCODE"||'%' AND
              "BD_ACCSUBJ"."PK_GLORGBOOK"='0001AI1000000003PENU')
       filter("BD_ACCSUBJ"."PK_GLORGBOOK"='0001AI1000000003PENU' AND
              "BD_ACCSUBJ"."SUBJCODE" LIKE "C"."SUBJCODE"||'%')


统计信息
----------------------------------------------------------
         19  recursive calls
          0  db block gets
        454  consistent gets
         86  physical reads
          0  redo size
       5242  bytes sent via SQL*Net to client
        278  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         86  rows processed