sql-server – 如何有效地检查多列上的EXISTS？

发布时间：2021-01-08 19:10:24 所属栏目：MsSql教程来源：网络整理

导读：这是我定期遇到的一个问题,还没有找到一个好的解决方案. 假设有以下表结构 CREATE TABLE T(A INT PRIMARY KEY,B CHAR(1000) NULL,C CHAR(1000) NULL) 并且要求是确定可空列B或C中的任何一个实际上是否包含任何NULL值(如果是,则确定哪一个()). 还假设该表包含

这是我定期遇到的一个问题,还没有找到一个好的解决方案.

假设有以下表结构

CREATE TABLE T
(
A INT PRIMARY KEY,B CHAR(1000) NULL,C CHAR(1000) NULL
)

并且要求是确定可空列B或C中的任何一个实际上是否包含任何NULL值(如果是,则确定哪一个()).

还假设该表包含数百万行(并且没有可用的列统计信息,因为我对此类查询的更通用的解决方案感兴趣).

我可以想到几种方法来解决这个问题,但都有缺点.

两个单独的EXISTS语句.这样做的好处是,只要找到NULL,就允许查询提前停止扫描.但如果两列实际上都不包含NULL,则会产生两次完整扫描.

单一聚合查询

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

这可以同时处理两个列,因此具有一次完整扫描的最坏情况.
缺点是,即使它在查询的早期遇到NULL,仍然会最终扫描整个表的其余部分.

用户变量

我可以想到第三种方法

BEGIN TRY
DECLARE @B INT,@C INT,@D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,@C = CASE WHEN C IS NULL THEN 1 ELSE @C END,/*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),ISNULL(@C,0)

但这不适合生产代码为the correct behavior for an aggregate concatenation query is undefined.并且通过抛出错误来终止扫描无论如何都是一个非常可怕的解决方案.

是否有另一种选择结合了上述方法的优势？

编辑

只是为了更新这个结果,我得到了迄今为??止提交的答案的读数(使用@ ypercube的测试数据)

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP,MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

对于@ Thomas的回答,我将TOP 3更改为TOP 2,可能允许它提前退出.我默认为该答案得到了一个并行计划,所以也尝试使用MAXDOP 1提示,以使读取数量与其他计划更具可比性.我对结果感到有些惊讶,因为在我之前的测试中,我看到了查询短路而没有读完整个表格.

我的测试数据的计划是短路

ypercube数据的计划是

因此,它为计划添加了一个阻塞排序运算符.我也试过HASH GROUP提示,但仍然最终读取所有行

因此关键似乎是获得一个哈希匹配(不同于流)运算符以允许此计划短路,因为其他备选方案将阻止并消耗所有行.我不认为有明确强制这一点,但显然是“in general,the optimiser chooses a Flow Distinct where it determines that fewer output rows are required than there are distinct values in the input set.”.

@ ypercube的数据在每列中只有1行,其中NULL值(表cardinality = 30300),进出运算符的估计行都是1.通过使谓词对优化器更加不透明,它生成了一个计划Flow Distinct运算符.

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b,CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT

编辑2

我发生的最后一个调整是,如果遇到NULL的第一行在B列和C列中都有NULL,则上面的查询仍然可能最终处理的行数超过必要的数量.它将继续扫描而不是立即退出.避免这种情况的一种方法是在扫描行时将其取消.所以我对Thomas Kejser’s answer的最终修改如下

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),(CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

谓词可能更好是WHERE(b IS NULL或c IS NULL)和NullExists IS NOT NULL但是对于先前的测试数据,一个人没有给我一个Flow Distinct的计划,而NullExists不是NULL一个(计划如下).

解决方法

怎么样：

SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS B,CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS C
  FROM T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT

（编辑：辽源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!