问Oracle SQL -比较空值时的连接性能

EN

您将返回一个包含任何具有空类的记录的叉积。这对你的结果合适吗？

我在11gR2中创建了两个示例查询：

WITH a as 
(select NULL as class, 5 as columna from dual
 UNION
 select NULL as class, 7 as columna from dual
 UNION
 select NULL as class, 9 as columna from dual
 UNION
 select 'X' as class, 3 as columna from dual
 UNION
 select 'Y' as class, 2 as columna from dual),
 b as 
 (select NULL as class, 2 as columnb from dual
 UNION
 select NULL as class, 15 as columnb from dual
 UNION
 select NULL as class, 5 as columnb from dual
 UNION
 select 'X' as class, 7 as columnb from dual
 UNION
 select 'Y' as class, 9 as columnb from dual)
    SELECT * from a JOIN b ON (a.class = b.class 
                              OR (a.class is null AND b.class is null))

当我在这个查询上运行EXPLAIN PLAN时，它表明表(在我的例子中是内联视图)是通过嵌套循环联接的。嵌套循环联接的操作方式是扫描一个表的第一行，然后扫描另一个表的每一行以查找匹配项，然后扫描第一个表的第二行，在第二个表中查找匹配项，依此类推。由于您不是在联接的OR部分中直接比较任何一个表，因此优化器必须使用嵌套循环。

在幕后，它可能看起来像这样：

• Get Table A，第1行。如果class为null，则将表A中的此行包括在结果集上。
• 仍在表A行1上时，在表B中搜索class为null的所有行。
• 对表A行1执行叉积，并且在表B
• 中找到的所有行都将这些行包括在结果集
• Get表A，第2行中。如果class为null，则将表A中的此行包括在结果集上。
• ....etc

当我将SELECT语句更改为SELECT * FROM a JOIN b ON NVL(a.class, 'N/A') = NVL(b.class, 'N/A')时，EXPLAIN指示使用散列联接。散列连接实质上是生成较小表的每个连接键的散列，然后扫描大表，在较小的表中查找匹配的每一行的散列。在本例中，由于它是一个简单的Equijoin，优化器可以毫无问题地散列驱动表的每一行。

在幕后，它可能看起来像这样：

• 遍历表A，将NULL类值转换为“N/A”
• 散列表A的每一行。
• 散列表A现在位于临时空间或内存中。
• 扫描表B，将NULL类值转换为“N/A”，然后计算值的散列。在哈希表中查找哈希(如果存在)，将表A和B的联接行包括在扫描B的结果set.
• Continue中。

如果对查询运行EXPLAIN计划，您可能会找到类似的结果。

即使最终结果是相同的，因为您没有使用"OR“连接第一个查询中的表，所以优化器不能使用更好的连接方法。如果驱动表很大，或者如果您强制对大型辅助表执行全表扫描，则嵌套循环可能会非常慢。

您可以使用ANSI函数在其他数据库系统中模拟COALESCE函数。这里真正的问题是，您正在尝试连接一个null值，您实际上应该有一个"NO CLASS“或一些其他方法来标识" NULL”类，即null = nothing而不是null = unknown。

在评论中回答您的问题的附录：

对于null查询，引擎将执行以下操作：

1. 从表A中读取行%1，类为null，转换为'N/A‘。
2. 表B有3行的类为null，请将每个null转换为’N/A‘。
3. 由于第一行与所有3行都匹配，因此向结果集中添加了3行，其中一行用于A1B1、A1B2、A1B3。
4. 从表A读取第2行，class为null，转换为'N/A‘/
5. 表B有3行class为null，请将每个null转换为’N/A‘。
6. 由于第二行与所有3行都匹配，因此向我们的结果集中添加了3行，其中一行用于A2B1、A2B2、A2B3。
7. 从表A读取第3行，class为null，转换为'N/A‘/
8. 表B有3行的class为null，请将每个null转换为’N/A‘。
9. 由于第三行与所有3行都匹配，因此向我们的结果集中添加了3行，其中一行对应于A3B1、A3B2、A3B3。10..第4行和第5行不为null，因此不会在连接的此部分中处理它们。

对于'N/A‘查询，引擎将执行以下操作：

1. 从表A读取行1，类为null，转换为'N/ A '，哈希此值。
2. 从表A读取行2，类为null，转换为'N/ A '，哈希此值。
3. 从表A读取行3，类为null，转换为'N/A'，哈希此值。
4. 从表A读取行4，类非null，哈希此值。
5. 从表A读取行5，类非null，对此值进行哈希处理。
6. 哈希表C现在位于内存中。
7. 从表B中读取行1，类为null，转换为“”N/A“”，将
8. 哈希值哈希到内存中的哈希表中，对于每个匹配项，将一行添加到结果集。“”发现了3行: A1、A2和A3。结果被添加到A1B1，A2B1，A3B1。
9. 从表B读取行2，类为null，转换为'N/A'，将value.
10. Compare散列值散列到内存中的散列表中，对于每个匹配，将一行添加到结果集。发现了3行: A1、A2和A3。结果被添加到A1B2，A2B2，A3B2。
11. 从表B读取行3，类为null，转换为'N/A'，将value.
12. Compare散列值散列到内存中的散列表中，对于每个匹配，将一行添加到结果集。发现了3行: A1、A2和A3。结果添加了A1B3、A2B3、A3B3。

在第一种情况下，因为每个null是不同的，所以数据库不使用优化(对于来自a的每一行，检查来自表b的每一行)。

在第二个案例数据库中，首先将所有空值更改为'N/A‘，然后只比较a.class和b.class，使用优化

比较Oracle中的空值非常耗时。null是未定义的值-一个Null与其他null不同。比较两个几乎相同的查询的结果：

select 1 from dual where null is null

select 1 from dual where null = null

只有带有特殊is null子句的第一个查询才返回正确答案。因此，空值不能被索引。

试试这个：

SELECT a from Table1 a JOIN JTable1 b ON a.class = b.class
where a.class is null
union all
SELECT a from Table1 a JOIN JTable1 b ON a.class = b.class
where b.class is null

应该变得更快