where条件でis nullまたはis NOT NULLオプティマイザを使用するとインデックスの使用は許可されませんか?
先日、ネット上で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 。
? …… ?