让优化器用上可为空的索引.
首先创建一张测试表：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 FOOCOMPUTE STATISTICSFOR TABLEFOR ALL INDEXESFOR 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让优化器使用为空的索引。
