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上，没有这个问题。

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

点击(此处)折叠或打开

1. sqlplus user/pwd@instance <<_end
   
2. 
   
3. WHENEVER SQLERROR CONTINUE;
   
4. 
   
5. DROP TABLE TESTINDEX CASCADE CONSTRAINTS;
   
6. 
   
7. WHENEVER SQLERROR EXIT 3;
   
8. 
   
9. CREATE TABLE TESTINDEX (
   
10.    COL1 char(4),
    
11.    COL2 char(4),
    
12.    COL3 char(8)
    
13. );
    
14. 
    
15. 
    
16.  CREATE INDEX INDEX_3 ON TESTINDEX
    
17.     (
    
18.       COL3 ASC
    
19. ---- , 100
    
20.     );
    
21. 
    
22.  CREATE INDEX INDEX_2 ON TESTINDEX
    
23.     (
    
24.       COL2 ASC
    
25. ---- ,100
    
26.     );
    
27. 
    
28. INSERT INTO TESTINDEX (COL1) VALUES ('AAAA');
    
29. INSERT INTO TESTINDEX (COL1,COL2) VALUES ('BBBB', '2222' );
    
30. INSERT INTO TESTINDEX (COL1,COL3) VALUES ('CCC1', 'CCCC3331' );
    
31. INSERT INTO TESTINDEX (COL1,COL3) VALUES ('CCC2', 'CCCC3332' );
    
32. INSERT INTO TESTINDEX (COL1,COL2,COL3) VALUES ('DDDD','4444','DDDD4444' );
    
33. 
    
34. commit;
    
35. 
    
36. select * from TESTINDEX;
    
37. 
    
38. exec dbms_stats.gather_table_stats(user, 'TESTINDEX', cascade=>TRUE);
    
39. 
    
40. select num_rows from user_indexes where index_name = 'INDEX_3';
    
41. select num_rows from user_indexes where index_name = 'INDEX_2';
    
42. 
    
43. set autotrace traceonly exp;
    
44. 
    
45. select COL1 from TESTINDEX where COL1 = 'AAAA';
    
46. select COL1 from TESTINDEX where COL1 = 'DDDD';
    

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

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

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

1. select COL2 from TESTINDEX where COL2 = '2222';
   
2. 
   
3. _end

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

点击(此处)折叠或打开

1. SQL> SQL>

   COL1         COL2         COL3

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

   AAAA

   BBBB         2222

   CCC1                      CCCC3331

   CCC2                      CCCC3332

   DDDD         4444         DDDD4444

    

   SQL> SQL>

   PL/SQL proceduresuccessfully completed.

    

   SQL> SQL>

     NUM_ROWS

   ----------

            3         

    

   SQL>

     NUM_ROWS

   ----------

            2         

    

   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 |

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

    

   PredicateInformation (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 |

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

    

   PredicateInformation (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 |

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

    

   PredicateInformation (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 |

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

    

   PredicateInformation (identified by operation id):

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

    

      1 - access("COL2"='2222')

    

   SQL>

   
   

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

点击(此处)折叠或打开

1. SQL> SQL>

   COL1         COL2         COL3

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

   AAAA

   BBBB         2222

   CCC1                      CCCC3331

   CCC2                      CCCC3332

   DDDD         4444         DDDD4444

    

   SQL> SQL>

   PL/SQL proceduresuccessfully 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 |

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

    

   PredicateInformation (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 |

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

    

   PredicateInformation (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 |

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

    

   PredicateInformation (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 |

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

    

   PredicateInformation (identified by operation id):

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

    

      1 - access("COL2"='2222')

    

   SQL>