sql - 测试NULL字段的最快方法(空检测)

Question

我使用两种不同的方法来检查数据库字段column_b是否不是NULL。哪个更快，为什么？

第一次查询

SELECT * FROM my_table
WHERE column_b IS NOT NULL;

第二查询

SELECT * FROM my_table
WHERE column_b = column_b;

column_b没有索引。

Answer 1

对于标准的标量数据类型，它们在Oracle上都是相同的(我尝试使用12c和11g)，因为您获得了相同执行计划的两倍。 (see MT0's answer for an edge case when this isn't true)

证明:

CREATE TABLE my_table (columnb NUMBER);

EXPLAIN PLAN FOR
SELECT *
FROM my_table
WHERE columnb IS NOT NULL;

SELECT *
FROM TABLE (dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT *
FROM my_table
WHERE columnb = columnb;

SELECT *
FROM TABLE (dbms_xplan.display);

在这两种情况下，我都会得到:

Plan hash value: 3804444429

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MY_TABLE |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COLUMNB" IS NOT NULL)

现在，即使您确实添加了索引...。

CREATE INDEX my_index ON my_table (columnb);

...您仍然会为两个查询获得相同的计划:

Plan hash value: 887433238

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | MY_INDEX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COLUMNB" IS NOT NULL)

如果该列是 NOT NULL怎么办？

让我们尝试一下:

DROP INDEX my_index; -- Get back to the initial situation
ALTER TABLE my_table MODIFY columnb NUMBER NOT NULL;

在两种情况下，我现在得到的计划都是这些，而整个谓词都被消除了:

Plan hash value: 3804444429

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MY_TABLE |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

结论

由于您没有从“聪明”的方法中获得任何好处，因此只需不要这样做，并编写 IS NOT NULL谓词就可以更清楚了。

顺便说一句，这是一个有趣的问题和优化类型 I've blogged about it and about similar optimisations more in depth here。