两个数据完全相同的表,表中数据大概300W行以上,MEASUREMENT_ID列值不重复的有7000左右,分别在这列上建立位图索引和普通索引,查看执行计划如下:
使用位图索引的执行计划:
SQL> select * from c1x_sig2 where MEASUREMENT_ID='120629151507000034';
已选择6316行。
已用时间: 00: 00: 00.44
执行计划
----------------------------------------------------------
Plan hash value: 1935475543
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15592 | 1827K| 3705 (1)| 00:00:45 |
| 1 | TABLE ACCESS BY INDEX ROWID | C1X_SIG2 | 15592 | 1827K| 3705 (1)| 00:00:45 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_SIG_UMID_BITMAP | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("MEASUREMENT_ID"='120629151507000034')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
510 consistent gets
94 physical reads
0 redo size
486125 bytes sent via SQL*Net to client
4968 bytes received via SQL*Net from client
423 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6316 rows processed
使用普通索引的执行计划:
SQL> select * from sig_test where MEASUREMENT_ID='120629151507000034';
已选择6316行。
已用时间: 00: 00: 01.03
执行计划
----------------------------------------------------------
Plan hash value: 2043660263
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7810 | 8839K| 172 (0) | 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | SIG_TEST | 7810 | 8839K| 172 (0) | 00:00:03 |
|* 2 | INDEX RANGE SCAN | IDX_SIG_UMID_TEST | 7810 | | 33 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MEASUREMENT_ID"='120629151507000034')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
562 recursive calls
0 db block gets
1161 consistent gets
402 physical reads
0 redo size
486125 bytes sent via SQL*Net to client
4968 bytes received via SQL*Net from client
423 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
6316 rows processed
这两次的语句看执行时间肯定是位图索引要快,但是为什么位图索引的执行计划里time达到了45秒?对我这情况到底是哪种索引更高效啊?