我使用两种不同的方法来检查数据库字段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),因为您获得了两倍的执行计划。(如果这不是真的,请参见MT0的答案)
证明:
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
谓词更清楚。
顺便说一句,这是一个有趣的问题和优化类型,我在此处进行了博客,并且在此处更深入地进行了有关类似的优化。
如果您使用对象,它们并不总是相同的:
sql小提琴
Oracle 11G R2架构设置:
CREATE TYPE coord AS OBJECT (
x NUMBER,
y NUMBER,
ORDER MEMBER FUNCTION match (l coord) RETURN INTEGER
);
/
CREATE TYPE BODY coord AS
ORDER MEMBER FUNCTION match (l coord) RETURN INTEGER IS
BEGIN
RETURN ( x * 100 + y )
- ( l.x * 100 + l.y);
END;
END;
/
CREATE TABLE table_name ( col1, col2 ) AS
SELECT 1, coord( 2, 3 ) FROM DUAL UNION ALL
SELECT 2, coord( 0, 2 ) FROM DUAL UNION ALL
SELECT 3, coord( NULL, 2 ) FROM DUAL UNION ALL
SELECT 4, NULL FROM DUAL
/
查询1 :
SELECT *
FROM table_name
WHERE col2 IS NOT NULL
结果:
| COL1 | COL2 |
|------|----------------------------|
| 1 | oracle.sql.STRUCT@10394576 |
| 2 | oracle.sql.STRUCT@11ddf9d5 |
| 3 | oracle.sql.STRUCT@595e9c7d |
解释计划:
Plan Hash Value : 3383972830
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 123 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TABLE_NAME | 3 | 123 | 3 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(SYS_OP_NOEXPAND("COL2") IS NOT NULL)
查询2 :
SELECT *
FROM table_name
WHERE col2 = col2
结果:
| COL1 | COL2 |
|------|----------------------------|
| 1 | oracle.sql.STRUCT@35c56f09 |
| 2 | oracle.sql.STRUCT@1f0e893f |
解释计划:
Plan Hash Value : 3383972830
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TABLE_NAME | 1 | 41 | 3 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("COORD"."MATCH"("COL2","COL2")=0)