代码之家 › 专栏 › 技术社区 › Baalback

选择查询0=0 Vs不存在

oracle sql

Baalback · 技术社区 · 7 年前

我有一个表包含1亿行,我发现下面的查询(a),这需要时间。我想把它改成(B),是一样的吗?

这个问题(A):

SELECT * 
FROM   tab1 
WHERE  code = 1 
       AND TYPE = 'A' 
       AND 0 = (SELECT Count(1) 
                FROM   tab1 
                WHERE  code = 1 
                       AND tr_type = 'APPROVE' 
                       AND security = 'Y')

与此类似(B)?

SELECT * 
FROM   tab1 
WHERE  code = 1 
       AND TYPE = 'A' 
       AND NOT EXISTS (SELECT 1 
                       FROM   tab1 
                       WHERE  code = 1 
                              AND tr_type = 'APPROVE' 
                              AND security = 'Y' 
                              AND ROWNUM = 1)

4 回复 | 直到 7 年前

YLG 7 年前

是的,同样的问题。

SELECT * 
  FROM tab1 
 WHERE code = 1 
   AND type = 'A' 
   AND 0 = (SELECT Count(1) 
              FROM tab1 
             WHERE code = 1 
              AND  tr_type = 'APPROVE' 
              AND  security = 'Y')

如果我们在上面的查询中获取内部查询的输出

SELECT Count(1) 
  FROM tab1 
 WHERE code = 1 
   AND tr_type = 'APPROVE' 
   AND security = 'Y'

我想,用 code = 1 TR_TYPE = 'APPROVE' 和 security = 'Y' 存在。所以输出将是1(假设只有一个匹配的记录存在)。

SELECT * 
  FROM tab1 
 WHERE code =1 
   AND type='A' 
   AND 0=1 (replacing the result)

SELECT * 
  FROM tab1 
 WHERE code = 1 
   AND type = 'A' 
   AND NOT EXISTS (SELECT 1 
                     FROM tab1 
                    WHERE code = 1 
                      AND tr_type = 'APPROVE' 
                      AND security = 'Y' 
                      AND rownum = 1)

SELECT * 
  FROM tab1 
 WHERE code =1 
   AND type='A' 
   AND NOT EXISTS (1)

Exists(If any record found) = true 在我们的情况下 Exists(1) = true 所以 Not Exist (1) = false

Himanshu 7 年前

是的,输出将是相同的,因为它只是一个可互换的方式来使用它,但在时间复杂度方面,第二个是优化。

George Joseph 7 年前

SELECT * 
  FROM tab1 
 WHERE code = 1 
   AND type = 'A' 
   AND NOT EXISTS (SELECT 1 
                     FROM tab1 b
                    WHERE b.code = 1 
                      AND b.tr_type = 'APPROVE' 
                      AND b.security = 'Y' 
                      AND rownum = 1)

Gordon Linoff 7 年前

我建议将查询编写为:

SELECT t1.* 
FROM tab1 t1
WHERE t1.code = 1 AND
      t1.TYPE = 'A' AND
      NOT EXISTS (SELECT 1 
                  FROM tab1 tt1
                  WHERE tt1.code = t1.code AND 
                        tt1.tr_type = 'APPROVE' 
                        tt1.security = 'Y'
                 );

这些变化是:

所有列都合格。
rownum = 1 条件已删除。

后者介于多余和危险之间。 EXISTS NOT EXISTS 正在查找任何匹配的行。包括…在内 rownum 影响优化器。

你的第一个版本必须找到匹配以计算计数。这可能比确定没有匹配项或找到第一个匹配项要昂贵得多。

为了提高性能,您需要启用索引 tab1(code, tr_type, security) 和 tab1(code, type)