我使用两种不同的方法来检查数据库字段column_b是否不为NULL.哪个更快,为什么？
第一个查询
SELECT * FROM my_table WHERE column_b IS NOT NULL;
第二个查询
SELECT * FROM my_table WHERE column_b = column_b;
column_b没有索引.
解决方法
对于标准的标量数据类型,它们在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.
