Oracle-in函数和exists函数对比

发表于 更新于 分类于 本文字数： 944 阅读时长 ≈ 1 分钟
Oracle sql中，如果使用in 进行条件判断，只能限制在1000元素以内，超过1000会报错，这时候应该使用exists。

SQL中 in 函数的优化语句

优化前：

1
2
3
4
5
6
SELECT *
  FROM PS_C_PS_QRY_RST S
 WHERE 1 = 1
   AND S.GP_PAYGROUP IN (SELECT C1.GP_PAYGROUP
                           FROM PS_C_PAYGROUP_TBL C1
                          WHERE C1.OPRID = 'SIE_ZL')

优化后：

1
2
3
4
5
6
7
8
9
SELECT *
  FROM PS_C_PS_QRY_RST S
 WHERE 1 = 1
   AND EXISTS
 (SELECT 'X'
          FROM PS_C_PAYGROUP_TBL C1
         WHERE  C1.OPRID = 'SIE_ZL'
           AND C1.GP_PAYGROUP = S.GP_PAYGROUP)

优化分析：利用 exists 的优化操作，会自动根据主表和关联表的数据量，进行优化处理，例如表二数据量大，则将二表作为基表，如果表一数据量大，则将一表作为基表。利用IN函数的话，只会把T表一作为基表。

同时，后来还发现一个漏洞，就是IN条件语句中的选项个数是有限制的，只能<=1000个，如果超过了，会报SQL-Error

1
2
3
4
5
6
7
8
9
10
11
12
13
UPDATE PS_C_EP_APPR_STEP ST
   SET ST.EMPLID = :1
 WHERE ST.EMPLID = DECODE(:2, ' ', ST.EMPLID, :3)
   AND ST.C_EP_DOC_ID IN ('100000004',
                          '100000005',
                          '100000006',
                          '100000007',
                          '100000008',
                          '100000009',
                          '100000010'…..)  --选项值不能超过1000个；
 /*报错信息：
SQL error. Function:  SQLExec  Error Position:  12116
Return:  1795 - ORA-01795: maximum number of expressions in a list is 1000 */

注意：不能无脑的使用EXISTS ，有时候反而会影响查询速度；要合理使用内联表、in、exists 等方式！