Oracle Index 列都为空导致全表查询

最近处理了一个把db2迁移到oracle的小问题。
Oracle的index上不允许所有构成index的列都是空；其实说“不允许”也是不准确的，如果某一条记录上恰好index对应的列都是空，那么你不会遇到任何错误。
但是会遇到一个性能问题。

当index上的所有的列在某条记录上都为空，那么，这个通过这个index是获取不到这条记录的。如果客户一个查询恰好要查询某些列为空（这些列在index中），那么index将不会被用到；那怎么得到结果的？答案是：全表查询！
太可怕了是不是。
那怎么解决这类问题呢？早有大侠给了解决方案：
在index的最后追加一列常值，这样一来，index再也不会出现所有列都是空的现象了。于是，所有记录就都可以被index获取到了。
creat index AAA on TAB_AAA ( col01, col01, 100);

DB2上，没有这个问题。

这里给一个脚本，可以跑一跑，体会一下。

sqlplus user/pwd@instance <<_end

WHENEVER SQLERROR CONTINUE;

DROP TABLE TESTINDEX CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE TESTINDEX (
   COL1 char(4),
   COL2 char(4),
   COL3 char(8)
);


 CREATE INDEX INDEX_3 ON TESTINDEX
    (
      COL3 ASC
----      , 100
    );

 CREATE INDEX INDEX_2 ON TESTINDEX
    (
      COL2 ASC
----      ,100
    );

INSERT INTO TESTINDEX (COL1) VALUES ('AAAA');
INSERT INTO TESTINDEX (COL1,COL2) VALUES ('BBBB', '2222' );
INSERT INTO TESTINDEX (COL1,COL3) VALUES ('CCC1', 'CCCC3331' );
INSERT INTO TESTINDEX (COL1,COL3) VALUES ('CCC2', 'CCCC3332' );
INSERT INTO TESTINDEX (COL1,COL2,COL3) VALUES ('DDDD','4444','DDDD4444' );

commit;

select * from TESTINDEX;

exec dbms_stats.gather_table_stats(user, 'TESTINDEX', cascade=>TRUE);

select num_rows from user_indexes where index_name = 'INDEX_3';
select num_rows from user_indexes where index_name = 'INDEX_2';

set autotrace traceonly exp;

select COL1 from TESTINDEX where COL1 = 'AAAA';
select COL1 from TESTINDEX where COL1 = 'DDDD';

      
       // 这条语句选择COL2为空的，那么就用了全表查询。

select count(*) from TESTINDEX where COL2 is null;

       // 这条语句永远都会用index的。

select COL2 from TESTINDEX where COL2 = '2222';

_end

结果如下，注意高亮部分的对比：

SQL> SQL>
				
				
					COL1         COL2         COL3
				
				
					------------
------------ ------------------------
				
				
					AAAA
				
				
					BBBB         2222
				
				
					CCC1                      CCCC3331
				
				
					CCC2                      CCCC3332
				
				
					DDDD         4444         DDDD4444
				
				
					 
				
				
					SQL> SQL>
				
				
					PL/SQL procedure
successfully completed.
				
				
					 
				
				
					SQL> SQL>
				
				
					  NUM_ROWS
				
				
					----------
				
				
					         3         <-- col3 不是空的记录在这里，不包括col3是空的。
				
				
					 
				
				
					SQL>
				
				
					  NUM_ROWS
				
				
					----------
				
				
					         2         <-- col2 不是空的记录在这里，不包括col2是空的。
				
				
					 
				
				
					SQL> SQL>
SQL> SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
3172374036
				
				
					 
				
				
					-------------------------------------------------------------------------------
				
				
					| Id  | Operation         | Name      | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					-------------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT  |          
|     1 |     5 |    
3   (0)| 00:00:01 |
				
				
					|*  1 | 
TABLE ACCESS FULL| TESTINDEX |    
1 |     5 |     3  
(0)| 00:00:01 |
				
				
					-------------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   1 - filter("COL1"='AAAA')
				
				
					 
				
				
					SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
3172374036
				
				
					 
				
				
					-------------------------------------------------------------------------------
				
				
					| Id  | Operation         | Name      | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					-------------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT  |          
|     1 |     5 |    
3   (0)| 00:00:01 |
				
				
					|*  1 | 
TABLE ACCESS FULL| TESTINDEX |    
1 |     5 |     3  
(0)| 00:00:01 |
				
				
					-------------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   1 - filter("COL1"='DDDD')
				
				
					 
				
				
					SQL> SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
2159720071
				
				
					 
				
				
					--------------------------------------------------------------------------------
				
				
					| Id  | Operation          | Name      | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					--------------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT   |          
|     1 |     3 |    
3   (0)| 00:00:01 |
				
				
					|   1 | 
SORT AGGREGATE    |           |    
1 |     3 |            |          |
				
				
					|*  2 |   TABLE ACCESS FULL| TESTINDEX |    
3 |     9 |     3  
(0)| 00:00:01 |
				
				
					--------------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   2 - filter("COL2" IS NULL)
				
				
					 
				
				
					SQL> SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
2605560524
				
				
					 
				
				
					----------------------------------------------------------------------------
				
				
					| Id  | Operation        | Name    | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					----------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT |         |    
1 |     3 |     1  
(0)| 00:00:01 |
				
				
					|*  1 |  INDEX RANGE SCAN| INDEX_2 |     1
|     3 |     1  
(0)| 00:00:01 |
				
				
					----------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   1 - access("COL2"='2222')
				
				
					 
				
				
					SQL>

如果把注释部分添加进来，结果如下：

SQL> SQL>
				
				
					COL1         COL2         COL3
				
				
					------------
------------ ------------------------
				
				
					AAAA
				
				
					BBBB         2222
				
				
					CCC1                      CCCC3331
				
				
					CCC2                      CCCC3332
				
				
					DDDD         4444         DDDD4444
				
				
					 
				
				
					SQL> SQL>
				
				
					PL/SQL procedure
successfully completed.
				
				
					 
				
				
					SQL> SQL>
				
				
					  NUM_ROWS
				
				
					----------
				
				
					         5    <-- 所有记录都在这里了
				
				
					 
				
				
					SQL>
				
				
					  NUM_ROWS
				
				
					----------
				
				
					         5
				
				
					 
				
				
					SQL> SQL>
SQL> SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
3172374036
				
				
					 
				
				
					-------------------------------------------------------------------------------
				
				
					| Id  | Operation         | Name      | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					-------------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT  |          
|     1 |     5 |    
3   (0)| 00:00:01 |
				
				
					|*  1 | 
TABLE ACCESS FULL| TESTINDEX |    
1 |     5 |     3  
(0)| 00:00:01 |
				
				
					-------------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   1 - filter("COL1"='AAAA')
				
				
					 
				
				
					SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
3172374036
				
				
					 
				
				
					-------------------------------------------------------------------------------
				
				
					| Id  | Operation         | Name      | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					-------------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT  |           |    
1 |     5 |     3  
(0)| 00:00:01 |
				
				
					|*  1 | 
TABLE ACCESS FULL| TESTINDEX |    
1 |     5 |     3  
(0)| 00:00:01 |
				
				
					-------------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   1 - filter("COL1"='DDDD')
				
				
					 
				
				
					SQL> SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
536543931
				
				
					 
				
				
					-----------------------------------------------------------------------------
				
				
					| Id  | Operation         | Name    | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					-----------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT  |        
|     1 |     3 |    
1   (0)| 00:00:01 |
				
				
					|   1 | 
SORT AGGREGATE   |         |    
1 |     3 |            |          |
				
				
					|*  2 |   INDEX RANGE SCAN| INDEX_2 |     3
|     9 |     1  
(0)| 00:00:01 |
				
				
					-----------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   2 - access("COL2" IS NULL)
				
				
					 
				
				
					SQL> SQL>
				
				
					Execution Plan
				
				
					----------------------------------------------------------
				
				
					Plan hash value:
2605560524
				
				
					 
				
				
					----------------------------------------------------------------------------
				
				
					| Id  | Operation        | Name    | Rows 
| Bytes | Cost (%CPU)| Time     |
				
				
					----------------------------------------------------------------------------
				
				
					|   0 | SELECT STATEMENT |         |    
1 |     3 |     1  
(0)| 00:00:01 |
				
				
					|*  1 |  INDEX RANGE SCAN| INDEX_2 |     1
|     3 |     1  
(0)| 00:00:01 |
				
				
					----------------------------------------------------------------------------
				
				
					 
				
				
					Predicate
Information (identified by operation id):
				
				
					---------------------------------------------------
				
				
					 
				
				
					   1 - access("COL2"='2222')
				
				
					 
				
				
					SQL>