前几天从网上下了一关于SQL优化的PDF,今天有空打开看了下,第一页就写着“任何SQL语句,只要在where条件中使用了is null或is NOT NULL,那么优化器就不允许使用索引了。”。以前没仔细研究过,今天做了下试验:
先看看环境:
- SQL> select * from v$version;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
- PL/SQL Release 11.2.0.1.0 - Production
- CORE 11.2.0.1.0 Production
- TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
- NLSRTL Version 11.2.0.1.0 - Production
表结构:
- SQL> desc t
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- OWNER NOT NULL VARCHAR2(30)
- OBJECT_NAME NOT NULL VARCHAR2(30)
- SUBOBJECT_NAME VARCHAR2(30)
- OBJECT_ID NOT NULL NUMBER
- DATA_OBJECT_ID NUMBER
- OBJECT_TYPE VARCHAR2(19)
- CREATED NOT NULL DATE
- LAST_DDL_TIME NOT NULL DATE
- TIMESTAMP VARCHAR2(19)
- STATUS VARCHAR2(7)
- TEMPORARY VARCHAR2(1)
- GENERATED VARCHAR2(1)
- SECONDARY VARCHAR2(1)
- NAMESPACE NOT NULL NUMBER
- EDITION_NAME VARCHAR2(30)
- ID NUMBER(38)
没有索引的情况
- SQL> select count(1) from t where object_id is not null;
- COUNT(1)
- ----------
- 1647872
- Elapsed: 00:00:01.45
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 5802 (1)| 00:01:10 |
- | 1 | SORT AGGREGATE | | 1 | 13 | | |
- | 2 | TABLE ACCESS FULL| T | 1874K| 23M| 5802 (1)| 00:01:10 |
- ---------------------------------------------------------------------------
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- SQL> select count(1) from t where object_id is null;
- COUNT(1)
- ----------
- 0
- Elapsed: 00:00:00.02
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1010173228
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
- | 1 | SORT AGGREGATE | | 1 | 13 | | |
- |* 2 | FILTER | | | | | |
- | 3 | TABLE ACCESS FULL| T | 1874K| 23M| 5802 (1)| 00:01:10 |
- ----------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(NULL IS NOT NULL)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
增加索引
- SQL> create index ix_t on t(object_id);
- Index created.
- Elapsed: 00:00:02.98
- SQL> select count(1) from t where object_id is null;
- COUNT(1)
- ----------
- 0
- Elapsed: 00:00:00.00
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 899206953
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
- | 1 | SORT AGGREGATE | | 1 | 13 | | |
- |* 2 | FILTER | | | | | |
- | 3 | INDEX FAST FULL SCAN| IX_T | 1874K| 23M| 963 (2)| 00:00:12 |
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(NULL IS NOT NULL)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- SQL> select count(1) from t where object_id is not null;
- COUNT(1)
- ----------
- 1647872
- Elapsed: 00:00:00.39
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 281895819
- ------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 963 (2)| 00:00:12 |
- | 1 | SORT AGGREGATE | | 1 | 13 | | |
- | 2 | INDEX FAST FULL SCAN| IX_T | 1874K| 23M| 963 (2)| 00:00:12 |
- ------------------------------------------------------------------------------
- Note
- -----
- - dynamic sampling used for this statement (level=2)
额,居然走索引了,再看看下面的例子
- SQL> select count(1) from t where object_id is not null and object_id=3;
- COUNT(1)
- ----------
- 128
- Elapsed: 00:00:00.02
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3165140883
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | 13 | | |
- |* 2 | INDEX RANGE SCAN| IX_T | 128 | 1664 | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("OBJECT_ID"=3)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- SQL> select count(1) from t where object_id is null and object_id=3;
- COUNT(1)
- ----------
- 0
- Elapsed: 00:00:00.02
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 899206953
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
- | 1 | SORT AGGREGATE | | 1 | 13 | | |
- |* 2 | FILTER | | | | | |
- | 3 | INDEX FAST FULL SCAN| IX_T | 1874K| 23M| 963 (2)| 00:00:12 |
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(NULL IS NOT NULL)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- SQL> select 1 from t where rownum=1 and object_id is null and object_id=3;
- no rows selected
- Elapsed: 00:00:00.01
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1200264783
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
- |* 1 | COUNT STOPKEY | | | | | |
- |* 2 | FILTER | | | | | |
- | 3 | INDEX FAST FULL SCAN| IX_T | 1874K| 23M| 963 (2)| 00:00:12 |
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(ROWNUM=1)
- 2 - filter(NULL IS NOT NULL)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
都是走索引的,再看下面两例子
- SQL> select * from t where object_id is not null and object_id=3;
- Elapsed: 00:00:00.00
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1513709308
- ------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 128 | 21888 | 116 (0)| 00:00:02 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T | 128 | 21888 | 116 (0)| 00:00:02 |
- |* 2 | INDEX RANGE SCAN | IX_T | 128 | | 3 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("OBJECT_ID"=3)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- SQL> select * from t where object_id is null and object_id=3;
- Elapsed: 00:00:00.00
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1322348184
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 171 | 0 (0)| |
- |* 1 | FILTER | | | | | |
- | 2 | TABLE ACCESS FULL| T | 1874K| 305M| 5825 (1)| 00:01:10 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(NULL IS NOT NULL)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
可见,where加is null/ is not null未必不走索引。什么?大声点……原因?我也不知道