测试无效场的最快方法(无原检测)



我使用两种不同的方法来检查数据库字段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)

相关内容

  • 没有找到相关文章

最新更新