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

第一个查询

SELECT * FROM my_table
WHERE column_b IS NOT NULL;

第二个查询

SELECT * FROM my_table
WHERE column_b = column_b;

column_b没有索引.

解决方法

证明：

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.

（编辑：李大同）

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