1.检索50%数据时，性能比较
SQL> select user_yn,count(*) from T_GROUP1 t
2 group by user_yn;
U COUNT(*)
- ----------
N 2298570
Y 2028150
--1.1全表扫描 select *
SQL> select /*+*/ * from scott.t_group1 t where t.user_yn='Y';
2028150 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2032K| 29M| 5770 (7)| 00:01:10 |
|* 1 | TABLE ACCESS FULL| T_GROUP1 | 2032K| 29M| 5770 (7)| 00:01:10 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."USER_YN"='Y')
Statistics
----------------------------------------------------------
2096 recursive calls
0 db block gets
163359 consistent gets
20320 physical reads
116 redo size
39507749 bytes sent via SQL*Net to client
1487683 bytes received via SQL*Net from client
135211 SQL*Net roundtrips to/from client
69 sorts (memory)
0 sorts (disk)
2028150 rows processed
--1.2全表扫描 sum()
SQL> select t.user_yn,sum(id)
2 from scott.t_group1 t
3 group by t.user_yn;
Execution Plan
----------------------------------------------------------
Plan hash value: 2782928935
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 6170 (13)| 00:01:15 |
| 1 | HASH GROUP BY | | 2 | 14 | 6170 (13)| 00:01:15 |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4327K| 28M| 5559 (3)| 00:01:07 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
21313 physical reads
0 redo size
520 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--1.3全表扫描 count()
SQL> select /*+no_index(t)*/ t.user_yn,count(*)
2 from scott.t_group1 t
3 group by t.user_yn;
Execution Plan
----------------------------------------------------------
Plan hash value: 2782928935
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 6170 (13)| 00:01:15 |
| 1 | HASH GROUP BY | | 2 | 4 | 6170 (13)| 00:01:15 |
| 2 | TABLE ACCESS FULL| T_GROUP1 | 4327K| 8451K| 5559 (3)| 00:01:07 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27957 consistent gets
16845 physical reads
0 redo size
517 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--W1 位图索引 select *
SQL> select /*+index_combine(t)*/ * from scott.t_group1 t where t.user_yn='Y';
2028150 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1637512934
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 2032K| 29M| 57953 (1)
| 00:11:36 |
| 1 | TABLE ACCESS BY INDEX ROWID | T_GROUP1 | 2032K| 29M| 57953 (1)
| 00:11:36 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | |
| |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_USER_YN | | |
| |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."USER_YN"='Y')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
139317 consistent gets
0 physical reads
0 redo size
48627930 bytes sent via SQL*Net to client
1487683 bytes received via SQL*Net from client
135211 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2028150 rows processed
--W2 位图索引SUM
SQL> select /*+index_combine(t)*/ t.user_yn,sum(id)
2 from scott.t_group1 t
3 group by t.user_yn;
Execution Plan
----------------------------------------------------------
Plan hash value: 2909726012
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 2 | 14 | 116K (1
)| 00:23:23 |
| 1 | SORT GROUP BY NOSORT | | 2 | 14 | 116K (1
)| 00:23:23 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_GROUP1 | 4327K| 28M| 116K (1
)| 00:23:23 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | |
| |
| 4 | BITMAP INDEX FULL SCAN | IDX_USER_YN | | |
| |
--------------------------------------------------------------------------------
-------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9256 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--W3 位图索引 count
SQL> select t.user_yn,count(*)
2 from scott.t_group1 t
3 group by t.user_yn;
Execution Plan
----------------------------------------------------------
Plan hash value: 854265386
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 2 | 4 | 91 (0)| 00
:00:02 |
| 1 | SORT GROUP BY NOSORT | | 2 | 4 | 91 (0)| 00
:00:02 |
| 2 | BITMAP CONVERSION COUNT| | 4327K| 8451K| 91 (0)| 00
:00:02 |
| 3 | BITMAP INDEX FULL SCAN| IDX_USER_YN | | | |
|
--------------------------------------------------------------------------------
--------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
92 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
【小结】
1. select * 性能比较
全表扫描的整体性能最好，COST仅为使用位图索引的10%；
但是位图索引的consistent gets要略少于全表扫描
2.sum 性能比较
全表扫描的整体性能好，COST仅为位图索引的5%
但是位图索引的consistent gets要比全表扫描少很多
2.检索2%数据，性能比较
2.1 结果集是500条记录
select /*+no_index(t)*/ t.biz_date,count(*)
from scott.t_group1 t
group by t.biz_date;
2.2 位图索引
SQL> select * from
2 scott.t_group1 t
3 where t.biz_date='86'
4 ;
6400 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1084705467
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 7200 | 105K| 1459 (1)| 0
0:00:18 |
| 1 | TABLE ACCESS BY INDEX ROWID | T_GROUP1 | 7200 | 105K| 1459 (1)| 0
0:00:18 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | |
|
|* 3 | BITMAP INDEX SINGLE VALUE | BIT_DATE | | | |
|
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."BIZ_DATE"='86')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
478 consistent gets
16 physical reads
0 redo size
152451 bytes sent via SQL*Net to client
5070 bytes received via SQL*Net from client
428 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6400 rows processed
2.3 全表扫描
SQL> select /*+no_index(t)*/ * from
2 scott.t_group1 t
3 where t.biz_date='86';
6400 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7200 | 105K| 5578 (3)| 00:01:07 |
|* 1 | TABLE ACCESS FULL| T_GROUP1 | 7200 | 105K| 5578 (3)| 00:01:07 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."BIZ_DATE"='86')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
28384 consistent gets
21592 physical reads
0 redo size
125584 bytes sent via SQL*Net to client
5070 bytes received via SQL*Net from client
428 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6400 rows processed
2.4 B-TREE索引
SQL> select /*+no_index(t)*/ * from
2 scott.t_group1 t
3 where t.biz_date='86';
6400 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7200 | 105K| 5578 (3)| 00:01:07 |
|* 1 | TABLE ACCESS FULL| T_GROUP1 | 7200 | 105K| 5578 (3)| 00:01:07 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."BIZ_DATE"='86')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
28384 consistent gets
21592 physical reads
0 redo size
125584 bytes sent via SQL*Net to client
5070 bytes received via SQL*Net from client
428 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6400 rows processed
SQL> select /*+index(t)*/ * from
2 scott.t_group1 t
3 where t.biz_date='86';
6400 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3047457754
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 7200 | 105K| 76 (2)| 00
:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_GROUP1 | 7200 | 105K| 76 (2)| 00
:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_BIZ | 7200 | | 17 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."BIZ_DATE"='86')
Statistics
----------------------------------------------------------
1705 recursive calls
0 db block gets
1334 consistent gets
13 physical reads
0 redo size
152451 bytes sent via SQL*Net to client
5070 bytes received via SQL*Net from client
428 SQL*Net roundtrips to/from client
52 sorts (memory)
0 sorts (disk)
6400 rows processed
【小结】数据为5%时，B-TREE的select * 效率高，BITMAP的count(*)效率高。
【总结】本实验及”笔记：表的连接和其他高级调整技术(连接方法)“的总结
1.位图索引计算COUNT(*)的效率非常高；
2.select * 或sum，不适合使用位图索引；
3.当表的尺寸很大，结果集很小时，多个位图索引联合使用的效果非常好（select *操作）;
4.与B-TREE索引比较，位图索引占用的空间非常小；位图索引的缺点是不支持高流量的DML操作。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7901922/viewspace-1060025/ ，如需转载，请注明出处，否则将追究法律责任。
