今天无意中看到了一个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)| | | |
--------------------------------------------------------------------------------------------------------------------------------------
| 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后面的查询条件是用户在界面任意选择的,可能的组合实在太多,建复合索引的可能性也不是很大。
希望后面有办法,或者有类似经验的帮忙跟个贴,指点一下。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9399028/viewspace-680186/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9399028/viewspace-680186/