让优化器用上可为空的索引.

首先创建一张测试表：

CREATE TABLE FOO(

  USERID NUMBER(20) NULL,

  USERNAME VARCHAR2(100),

  USERDEPT NUMBER(20),

  USERDEPTNAME VARCHAR2(100)

)

/

模拟上10万条数据进去：

BEGIN

  FOR I IN 0..100000 LOOP

    INSERT INTO FOO

    VALUES

    (I,'ZIWEN'||i,MOD(I,30),'USERDEPTNAME'||MOD(I,30));

  END LOOP;

  IF MOD(I,100) =0 THEN

    COMMIT;

  END IF;

END;

/

在可以为NULL的USERID上面增加一个索引：

CREATE INDEX INDEX_FOO_USERID ON FOO(USERID);

进行表分析：

ANALYZE TABLE FOO

COMPUTE STATISTICS

FOR TABLE

FOR ALL INDEXES

FOR ALL INDEXED COLUMNS;

执行下面的语句，按照道理而言，应该走INDEX，INDEX RANGE SCAN,实际上也是走的索引范围扫描。

SELECT USERID FROM FOO WHERE USERID = 100;

修改其中的某些USERID为空再试一下，仍旧走的是索引范围扫描。

UPDATE FOO SET FOO.USERID = NULL WHERE MOD(USERID,1000)=0;

给查询的SQL增加HINT提示，看一下执行计划：

SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;

执行计划如下：

Plan hash value: 1245013993

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   100K|  3515K|   171   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| FOO  |   100K|  3515K|   171   (1)| 00:00:03 |

--------------------------------------------------------------------------

为什么即使给了HINT也不走索引INDEX_FOO_USERID呢？是因为USERID允许为空么？

SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO WHERE USERID IS NOT NULL;

执行计划如下：

------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                  |   100K|  3515K|   756   (1)| 00:00:10 |

|   1 |  TABLE ACCESS BY INDEX ROWID| FOO              |   100K|  3515K|   756   (1)| 00:00:10 |

|*  2 |   INDEX FULL SCAN           | INDEX_FOO_USERID |   100K|       |   224   (1)| 00:00:03 |

------------------------------------------------------------------------------------------------

增加了IS NOT NULL之后，索引走了INDEX_FOO_USERID。

换一个角度，如果让USERID列，设置为非空的话，会不会走索引呢？

UPDATE FOO SET FOO.USERID =990000 WHERE USERID IS NULL;

ALTER TABLE FOO MODIFY(USERID NOT NULL); 

然后再执行刚刚的语句，只是这个时候不加IS NOT NULL：

SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;

这个时候的执行计划：

------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                  |   100K|  3515K|   756   (1)| 00:00:10 |

|   1 |  TABLE ACCESS BY INDEX ROWID| FOO              |   100K|  3515K|   756   (1)| 00:00:10 |

|   2 |   INDEX FULL SCAN           | INDEX_FOO_USERID |   100K|       |   224   (1)| 00:00:03 |

------------------------------------------------------------------------------------------------

发现通过HINT，优化器走了索引。

将索引设置为NULL，但是里面不存在NULL值的时候：

ALTER TABLE FOO MODIFY(USERID NULL); 

SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;

执行计划如下：

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   100K|  3515K|   171   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| FOO  |   100K|  3515K|   171   (1)| 00:00:03 |

--------------------------------------------------------------------------

这里得到的一个结论就是：

索引能不为空就不为空，也可以通过在索引列加上IS NOT NULL让优化器使用为空的索引。