BITMAP CONVERSION TO ROWIDS

今天無意中看到了一個SQL語句的執行計劃，出現了BITMAP CONVERSION TO ROWIDS，感覺甚是奇怪，這個表上明明沒有bitmap index，怎麼還會出現BITMAP字樣，而且還是很陌生的BITMAP CONVERSION TO ROWIDS，把執行計劃貼出來：

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |       |       |   160 (100)|          |       |       |
|   1 |  COUNT STOPKEY                           |                           |       |       |            |          |       |       |
|   2 |   VIEW                                   |                           |     1 |   970 |   160   (4)| 00:00:02 |       |       |
|   3 |    SORT ORDER BY STOPKEY                 |                           |     1 |   170 |   160   (4)| 00:00:02 |       |       |
|   4 |     NESTED LOOPS                         |                           |     1 |   170 |   159   (3)| 00:00:02 |       |       |
|   5 |      NESTED LOOPS OUTER                  |                           |     1 |   142 |   158   (3)| 00:00:02 |       |       |
|   6 |       NESTED LOOPS                       |                           |     1 |   129 |   157   (3)| 00:00:02 |       |       |
|   7 |        PARTITION LIST SINGLE             |                           |     1 |   111 |   155   (3)| 00:00:02 |   KEY |   KEY |
|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| T_STUDENT                 |     1 |   111 |   155   (3)| 00:00:02 |   KEY |   KEY |
|   9 |          BITMAP CONVERSION TO ROWIDS     |                           |       |       |            |          |       |       |
|  10 |           BITMAP AND                     |                           |       |       |            |          |       |       |
|  11 |            BITMAP CONVERSION FROM ROWIDS |                           |       |       |            |          |       |       |
|  12 |             INDEX RANGE SCAN             | INDEX_SUTDENT_STATUSAPP   |  2718 |       |     9   (0)| 00:00:01 |   KEY |   KEY |
|  13 |            BITMAP CONVERSION FROM ROWIDS |                           |       |       |            |          |       |       |
|  14 |             INDEX RANGE SCAN             | INDEX_STUDENT_CLAZZ       |  2718 |       |   121   (2)| 00:00:02 |   KEY |   KEY |
|  15 |        TABLE ACCESS BY INDEX ROWID       | T_SCHOOL_SPECIALTY        |     1 |    18 |     2   (0)| 00:00:01 |       |       |
|  16 |         INDEX UNIQUE SCAN                | PK_T_SCHOOL_SPECIALTY     |     1 |       |     1   (0)| 00:00:01 |       |       |
|  17 |       TABLE ACCESS BY INDEX ROWID        | T_STANDARD_SUB            |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|  18 |        INDEX UNIQUE SCAN                 | PK_T_STANDARD_SUB         |     1 |       |     0   (0)|          |       |       |
|  19 |      TABLE ACCESS BY INDEX ROWID         | T_SCHOOL                  |     1 |    28 |     1   (0)| 00:00:01 |       |       |
|  20 |       INDEX UNIQUE SCAN                  | PK_T_SCHOOL               |     1 |       |     0   (0)|          |       |       |
--------------------------------------------------------------------------------------------------------------------------------------

 

後來發現，是SQL語句的Where條件後面，多了一個條件的原因了，且該條件欄位上有索引，去掉該條件，保留前面2個條件，也都有索引，就會走一般的TABLE ACCESS BY LOCAL INDEX ROWID和INDEX RANGE SCAN，增加另一個有索引的條件，則又會出現上面的BITMAP CONVERSION TO ROWIDS。

 

對於這種情況，目前還沒找到很好的解決辦法，刪除索引肯定是不行的，where後面的查詢條件是使用者在介面任意選擇的，可能的組合實在太多，建複合索引的可能性也不是很大。

 

希望後面有辦法，或者有類似經驗的幫忙跟個貼，指點一下。