oralce SQL执行对比-优快云博客

本文链接：https://blog.youkuaiyun.com/javabaidu/article/details/84525700

本文探讨了在特定数据库环境中，使用不同方法查询特定条件数据的效率对比，包括使用exists、count(*)与inner join等策略，通过PLSQL执行计划分析得出最优执行方案。

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

要查询一个人的身高、体重为数字，而体重指数为空的数据。

类似的查询，可能会很多，例如：

userInfo表结构为：
[table]
|id|user_id|name|code|val
|1|35|王五|age|22
|2|35|王五|mobile|18612341234
|3|35|王五|idno|331228198501024423
|-|- |- |- |-
|4|37|王六|age|19
|5|37|王六|mobile|13212341234
|6|37|王六|idno|null
|-|- |- |- |-
|8|39|王七|age|18
|9|39|王七|mobile|18612341234
|10|39|王七|idno|13312341234
[/table]

查询
年龄>18
手机号不为空
哪些人没填写身份证号

正确的查询结果是“王六”

三条语句，完成同样的查询功能，执行效率对比
表结构

[img]http://dl2.iteye.com/upload/attachment/0093/7836/991f93d7-2d62-3a31-b7a0-1c2ef781d371.jpg[/img]

第一种写法，使用exists进行查询

 select count(*)
   from pis_checkitemindexresult c
  where 
    exists (select 1
           from pis_checkitemindexresult c2
          where c2.itemindexmiscode = '1.2.4.1' --身高
            and f_is_num(c2.itemindexresultvalue) = 'T'
            and c2.workno = c.workno)
    and exists (select 1
           from pis_checkitemindexresult c3
          where c3.itemindexmiscode = '1.2.4.2' --体重
            and f_is_num(c3.itemindexresultvalue) = 'T'
            and c3.workno = c.workno)
    and c.itemindexmiscode = '1.2.4.3' --体重指数
    and c.itemindexresultvalue is null

PLSQL执行计划：

[img]http://dl2.iteye.com/upload/attachment/0093/7793/dfeeeb39-17ab-309d-8aaa-74d0bc79db27.jpg[/img]

第二种写法，使用count(*)来查询

select count(*)
   from pis_checkitemindexresult c
  where
  (select count(*)
     from pis_checkitemindexresult c2
    where c2.itemindexmiscode = '1.2.4.1' --身高
      and f_is_num(c2.itemindexresultvalue) = 'T'
      and c2.workno = c.workno) > 0

  and (select count(*)
     from pis_checkitemindexresult c3
    where c3.itemindexmiscode = '1.2.4.2' --体重
      and f_is_num(c3.itemindexresultvalue) = 'T'
      and c3.workno = c.workno) > 0

  and c.itemindexmiscode = '1.2.4.3' --体重指数
  and c.itemindexresultvalue is null

PLSQL执行计划：

[img]http://dl2.iteye.com/upload/attachment/0093/7795/2bf5c08d-8762-3399-94f8-6b1279ae0df8.jpg[/img]

第三种写法：使用inner join来关联查询

select count(*)
  from pis_checkitemindexresult c
  inner join pis_checkitemindexresult c2 on c.workno = c2.workno
  inner join pis_checkitemindexresult c3 on c.workno = c3.workno
 where

   c2.itemindexmiscode = '1.2.4.1'
   and f_is_num(c2.itemindexresultvalue)='T'


   and c3.itemindexmiscode = '1.2.4.2'
   and f_is_num(c3.itemindexresultvalue)='T'


   and c.itemindexmiscode = '1.2.4.3'
   and c.itemindexresultvalue is null