前几天从网上下了一关于SQL优化的PDF，今天有空打开看了下，第一页就写着“任何SQL语句，只要在where条件中使用了is null或is NOT NULL，那么优化器就不允许使用索引了。”。以前没仔细研究过，今天做了下试验：

先看看环境： 

 
  
  1. SQL> select * from v$version; 
  2.   
  3.  BANNER 
  4.  -------------------------------------------------------------------------------- 
  5.  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 
  6.  PL/SQL Release 11.2.0.1.0 - Production 
  7.  CORE    11.2.0.1.0      Production 
  8.  TNS for 64-bit Windows: Version 11.2.0.1.0 - Production 
  9.  NLSRTL Version 11.2.0.1.0 - Production 
 
  
表结构： 
 
  
 
  
  1. SQL> desc t 
  2.   Name                                      Null?    Type 
  3.   ----------------------------------------- -------- ---------------------------- 
  4.   OWNER                                     NOT NULL VARCHAR2(30) 
  5.   OBJECT_NAME                               NOT NULL VARCHAR2(30) 
  6.   SUBOBJECT_NAME                                     VARCHAR2(30) 
  7.   OBJECT_ID                                 NOT NULL NUMBER 
  8.   DATA_OBJECT_ID                                     NUMBER 
  9.   OBJECT_TYPE                                        VARCHAR2(19) 
  10.   CREATED                                   NOT NULL DATE 
  11.   LAST_DDL_TIME                             NOT NULL DATE 
  12.   TIMESTAMP                                          VARCHAR2(19) 
  13.   STATUS                                             VARCHAR2(7) 
  14.   TEMPORARY                                          VARCHAR2(1) 
  15.   GENERATED                                          VARCHAR2(1) 
  16.   SECONDARY                                          VARCHAR2(1) 
  17.   NAMESPACE                                 NOT NULL NUMBER 
  18.   EDITION_NAME                                       VARCHAR2(30) 
  19.   ID                                                 NUMBER(38) 
 
  
没有索引的情况
 
  
 
  
  1. SQL> select count(1) from t where object_id is not null; 
  2.   
  3.    COUNT(1)                                                                       
  4.  ----------                                                                       
  5.     1647872                                                                       
  6.   
  7.  Elapsed: 00:00:01.45 
  8.   
  9.  Execution Plan 
  10.  ----------------------------------------------------------                       
  11.  Plan hash value: 2966233522                                                      
  12.                                                                                   
  13.  ---------------------------------------------------------------------------      
  14.  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
  15.  ---------------------------------------------------------------------------      
  16.  |   0 | SELECT STATEMENT   |      |     1 |    13 |  5802   (1)| 00:01:10 |      
  17.  |   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |      
  18.  |   2 |   TABLE ACCESS FULL| T    |  1874K|    23M|  5802   (1)| 00:01:10 |      
  19.  ---------------------------------------------------------------------------      
  20.                                                                                   
  21.  Note                                                                             
  22.  -----                                                                            
  23.     - dynamic sampling used for this statement (level=2)                          
  24.   
  25.  SQL> select count(1) from t where object_id is  null; 
  26.   
  27.    COUNT(1)                                                                       
  28.  ----------                                                                       
  29.           0                                                                       
  30.   
  31.  Elapsed: 00:00:00.02 
  32.   
  33.  Execution Plan 
  34.  ----------------------------------------------------------                       
  35.  Plan hash value: 1010173228                                                      
  36.                                                                                   
  37.  ----------------------------------------------------------------------------     
  38.  | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
  39.  ----------------------------------------------------------------------------     
  40.  |   0 | SELECT STATEMENT    |      |     1 |    13 |     0   (0)|          |     
  41.  |   1 |  SORT AGGREGATE     |      |     1 |    13 |            |          |     
  42.  |*  2 |   FILTER            |      |       |       |            |          |     
  43.  |   3 |    TABLE ACCESS FULL| T    |  1874K|    23M|  5802   (1)| 00:01:10 |     
  44.  ----------------------------------------------------------------------------     
  45.                                                                                   
  46.  Predicate Information (identified by operation id):                              
  47.  ---------------------------------------------------                              
  48.                                                                                   
  49.     2 - filter(NULL IS NOT NULL)                                                  
  50.                                                                                   
  51.  Note                                                                             
  52.  -----                                                                            
  53.     - dynamic sampling used for this statement (level=2)     
 
  
增加索引
 
  
 
  
  1. SQL> create index ix_t on t(object_id); 
  2.   
  3.  Index created. 
  4.   
  5.  Elapsed: 00:00:02.98 
  6.  SQL> select count(1) from t where object_id is  null; 
  7.   
  8.    COUNT(1)                                                                       
  9.  ----------                                                                       
  10.           0                                                                       
  11.   
  12.  Elapsed: 00:00:00.00 
  13.   
  14.  Execution Plan 
  15.  ----------------------------------------------------------                       
  16.  Plan hash value: 899206953                                                       
  17.                                                                                   
  18.  -------------------------------------------------------------------------------  
  19.  | Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  20.  -------------------------------------------------------------------------------  
  21.  |   0 | SELECT STATEMENT       |      |     1 |    13 |     0   (0)|          |  
  22.  |   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |  
  23.  |*  2 |   FILTER               |      |       |       |            |          |  
  24.  |   3 |    INDEX FAST FULL SCAN| IX_T |  1874K|    23M|   963   (2)| 00:00:12 |  
  25.  -------------------------------------------------------------------------------  
  26.                                                                                   
  27.  Predicate Information (identified by operation id):                              
  28.  ---------------------------------------------------                              
  29.                                                                                   
  30.     2 - filter(NULL IS NOT NULL)                                                  
  31.                                                                                   
  32.  Note                                                                             
  33.  -----                                                                            
  34.     - dynamic sampling used for this statement (level=2)                          
  35.   
  36.  SQL> select count(1) from t where object_id is not null; 
  37.   
  38.    COUNT(1)                                                                       
  39.  ----------                                                                       
  40.     1647872                                                                       
  41.   
  42.  Elapsed: 00:00:00.39 
  43.   
  44.  Execution Plan 
  45.  ----------------------------------------------------------                       
  46.  Plan hash value: 281895819                                                       
  47.                                                                                   
  48.  ------------------------------------------------------------------------------   
  49.  | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
  50.  ------------------------------------------------------------------------------   
  51.  |   0 | SELECT STATEMENT      |      |     1 |    13 |   963   (2)| 00:00:12 |   
  52.  |   1 |  SORT AGGREGATE       |      |     1 |    13 |            |          |   
  53.  |   2 |   INDEX FAST FULL SCAN| IX_T |  1874K|    23M|   963   (2)| 00:00:12 |   
  54.  ------------------------------------------------------------------------------   
  55.                                                                                   
  56.  Note                                                                             
  57.  -----                                                                            
  58.     - dynamic sampling used for this statement (level=2)      
 
  
额，居然走索引了，再看看下面的例子
 
  
 
  
  1. SQL> select count(1) from t where object_id is not null and object_id=3; 
  2.   
  3.    COUNT(1)                                                                       
  4.  ----------                                                                       
  5.         128                                                                       
  6.   
  7.  Elapsed: 00:00:00.02 
  8.   
  9.  Execution Plan 
  10.  ----------------------------------------------------------                       
  11.  Plan hash value: 3165140883                                                      
  12.                                                                                   
  13.  --------------------------------------------------------------------------       
  14.  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |       
  15.  --------------------------------------------------------------------------       
  16.  |   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |       
  17.  |   1 |  SORT AGGREGATE   |      |     1 |    13 |            |          |       
  18.  |*  2 |   INDEX RANGE SCAN| IX_T |   128 |  1664 |     3   (0)| 00:00:01 |       
  19.  --------------------------------------------------------------------------       
  20.                                                                                   
  21.  Predicate Information (identified by operation id):                              
  22.  ---------------------------------------------------                              
  23.                                                                                   
  24.     2 - access("OBJECT_ID"=3)                                                     
  25.                                                                                   
  26.  Note                                                                             
  27.  -----                                                                            
  28.     - dynamic sampling used for this statement (level=2)                          
  29.   
  30.  SQL> select count(1) from t where object_id is  null and object_id=3; 
  31.   
  32.    COUNT(1)                                                                       
  33.  ----------                                                                       
  34.           0                                                                       
  35.   
  36.  Elapsed: 00:00:00.02 
  37.   
  38.  Execution Plan 
  39.  ----------------------------------------------------------                       
  40.  Plan hash value: 899206953                                                       
  41.                                                                                   
  42.  -------------------------------------------------------------------------------  
  43.  | Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  44.  -------------------------------------------------------------------------------  
  45.  |   0 | SELECT STATEMENT       |      |     1 |    13 |     0   (0)|          |  
  46.  |   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |  
  47.  |*  2 |   FILTER               |      |       |       |            |          |  
  48.  |   3 |    INDEX FAST FULL SCAN| IX_T |  1874K|    23M|   963   (2)| 00:00:12 |  
  49.  -------------------------------------------------------------------------------  
  50.                                                                                   
  51.  Predicate Information (identified by operation id):                              
  52.  ---------------------------------------------------                              
  53.                                                                                   
  54.     2 - filter(NULL IS NOT NULL)                                                  
  55.                                                                                   
  56.  Note                                                                             
  57.  -----                                                                            
  58.     - dynamic sampling used for this statement (level=2)                          
  59.   
  60.  SQL> select 1 from t where rownum=1 and object_id is  null and object_id=3; 
  61.   
  62.  no rows selected 
  63.   
  64.  Elapsed: 00:00:00.01 
  65.   
  66.  Execution Plan 
  67.  ----------------------------------------------------------                       
  68.  Plan hash value: 1200264783                                                      
  69.                                                                                   
  70.  -------------------------------------------------------------------------------  
  71.  | Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  72.  -------------------------------------------------------------------------------  
  73.  |   0 | SELECT STATEMENT       |      |     1 |    13 |     0   (0)|          |  
  74.  |*  1 |  COUNT STOPKEY         |      |       |       |            |          |  
  75.  |*  2 |   FILTER               |      |       |       |            |          |  
  76.  |   3 |    INDEX FAST FULL SCAN| IX_T |  1874K|    23M|   963   (2)| 00:00:12 |  
  77.  -------------------------------------------------------------------------------  
  78.                                                                                   
  79.  Predicate Information (identified by operation id):                              
  80.  ---------------------------------------------------                              
  81.                                                                                   
  82.     1 - filter(ROWNUM=1)                                                          
  83.     2 - filter(NULL IS NOT NULL)                                                  
  84.                                                                                   
  85.  Note                                                                             
  86.  -----                                                                            
  87.     - dynamic sampling used for this statement (level=2)    
 
  
都是走索引的，再看下面两例子 
 
  
 
  
  1. SQL> select * from t where  object_id is not  null and object_id=3; 
  2.  Elapsed: 00:00:00.00 
  3.   
  4.  Execution Plan 
  5.  ---------------------------------------------------------- 
  6.  Plan hash value: 1513709308 
  7.   
  8.  ------------------------------------------------------------------------------------ 
  9.  | Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  10.  ------------------------------------------------------------------------------------ 
  11.  |   0 | SELECT STATEMENT            |      |   128 | 21888 |   116   (0)| 00:00:02 | 
  12.  |   1 |  TABLE ACCESS BY INDEX ROWID| T    |   128 | 21888 |   116   (0)| 00:00:02 | 
  13.  |*  2 |   INDEX RANGE SCAN          | IX_T |   128 |       |     3   (0)| 00:00:01 | 
  14.  ------------------------------------------------------------------------------------ 
  15.   
  16.  Predicate Information (identified by operation id): 
  17.  --------------------------------------------------- 
  18.   
  19.     2 - access("OBJECT_ID"=3) 
  20.   
  21.  Note 
  22.  ----- 
  23.     - dynamic sampling used for this statement (level=2) 
  24.   
  25.  SQL> select * from t where  object_id is  null and object_id=3; 
  26.  Elapsed: 00:00:00.00 
  27.   
  28.  Execution Plan 
  29.  ---------------------------------------------------------- 
  30.  Plan hash value: 1322348184 
  31.   
  32.  --------------------------------------------------------------------------- 
  33.  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  34.  --------------------------------------------------------------------------- 
  35.  |   0 | SELECT STATEMENT   |      |     1 |   171 |     0   (0)|          | 
  36.  |*  1 |  FILTER            |      |       |       |            |          | 
  37.  |   2 |   TABLE ACCESS FULL| T    |  1874K|   305M|  5825   (1)| 00:01:10 | 
  38.  --------------------------------------------------------------------------- 
  39.   
  40.  Predicate Information (identified by operation id): 
  41.  --------------------------------------------------- 
  42.   
  43.     1 - filter(NULL IS NOT NULL) 
  44.   
  45.  Note 
  46.  ----- 
  47.     - dynamic sampling used for this statement (level=2) 
 
  

可见，where加is null/ is not null未必不走索引。
 
  
什么？大声点……原因？我也不知道