oracle内连接时列的值是null,Oracle SQL - 比较空值时的JOIN性能

您将使用任何具有null类的记录返回交叉产品。你的结果好吗？

我在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时，它表示表(我的情况下是内联视图)通过NESTED LOOPS加入。 NESTED LOOPS连接通过扫描一个表的第一行，然后扫描另一个表的每一行进行匹配，然后扫描第一个表的第二行，在第二个表上查找匹配等等来进行操作。因为您不是直接比较无论是JOIN的OR部分中的表，优化器都必须使用NESTED LOOPS。

在幕后它可能看起来像：

获取表A，第1行。如果class为null，则在结果集中包含表A中的该行。

仍然在表A第1行上，搜索表B，其中所有行都为class。

在表A第1行和表B中的所有行上执行交叉产品

在结果集中包含这些行

获取表A，第2行。如果class为null，则在结果集中包含表A中的此行。

.... etc

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

在幕后它可能看起来像：

通过表A，将NULL类值转换为'N / A'

随时随地散列表格A的每一行。

哈希表A现在位于临时空间或内存中。

扫描表B，将NULL类值转换为'N / A'，然后计算值的哈希值。散列表中的查找散列(如果存在)包括结果集中表A和B的连接行。

继续扫描B.

如果您对查询运行EXPLAIN PLAN，您可能会发现类似的结果。

尽管最终结果是相同的，因为您没有使用“OR”连接第一个查询中的表，优化程序无法使用更好的连接方法。如果驱动表很大，或者如果要对大型辅助表强制进行全表扫描，则嵌套的LOOPS可能会非常慢。

您可以使用ANSI COALESCE函数来模拟其他数据库系统中的NVL oracle函数。这里真正的问题是你试图加入一个NULL值，你真的应该有一个“NO CLASS”或一些其他方法来识别null = null而不是null = unknown的“null”类。

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

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

从表A中读取第1行，类为空，转换为“N / A”。

表B有3行，其中class为null，将每个null转换为'N / A'。

由于第一行与所有3行匹配，因此我们的结果集中添加了3行，一行用于A1B1，A1B2，A1B3。

读取第2行从表A中，类为空，转换为“N / A”/

表B有3行，其中class为null，将每个null转换为'N / A'。

由于第二行与所有3行匹配，因此我们的结果集中添加了3行，一行用于A2B1，A2B2，A2B3。

读取第3行从表A中，类为空，转换为“N / A”/

表B有3行，其中class为null，将每个null转换为'N / A'。

由于第三行与所有3行匹配，因此我们的结果集中添加了3行，一行用于A3B1，A3B2，A3B3。

10 ..第4行和第5行不为空，因此不会在此部分连接中处理它们。

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

从表A读取第1行，类为空，转换为'N / A'，哈希此值。

从表A读取第2行，类为空，转换为'N / A'，哈希此值。

从表A读取第3行，类为空，转换为'N / A'，哈希此值。

从表A读取第4行，类not null，哈希此值。

从表A读取第5行，类not null，哈希此值。

哈希表C现在在内存中。

从表B中读取第1行，类为空，转换为“N / A”，哈希值。

将散列值与内存中的散列表进行比较，对于每个匹配，在结果集中添加一行。找到3行，A1，A2和A3。结果添加A1B1，A2B1，A3B1。

从表B读取第2行，类为空，转换为'N / A'，哈希值。

将散列值与内存中的散列表进行比较，对于每个匹配，在结果集中添加一行。找到3行，A1，A2和A3。结果添加了A1B2，A2B2，A3B2。

从表B中读取第3行，类为空，转换为'N / A'，哈希值。

将散列值与内存中的散列表进行比较，对于每个匹配，在结果集中添加一行。找到3行，A1，A2和A3。结果添加A1B3，A2B3，A3B3。