1、入门
SQL> create table t1
2 pctfree 70
3 pctused 30
4 nologging
5 as
6 select
7 mod((rownum-1),20) n1, -- 散布值为20
8 trunc((rownum-1)/500) n2, -- 集群值为20
9 --
10 mod((rownum-1),25) n3, -- 散布值为25
11 trunc((rownum-1)/400) n4, -- 集群值为25
12 --
13 mod((rownum-1),25) n5, -- B树散布值为25
14 trunc((rownum-1)/400) n6, -- B树集群值为25
15 --
16 lpad(rownum,10,'0') small_vc, --选择的目标
17 rpad('x',220) padding --浪费空间
18 from
19 all_objects
20 where
21 rownum <= 10000
22 ;
表已创建。
SQL> create bitmap index t1_i1 on t1(n1) nologging pctfree 90;
索引已创建。
SQL> create bitmap index t1_i2 on t1(n2) nologging pctfree 90;
索引已创建。
SQL> create bitmap index t1_i3 on t1(n3) nologging pctfree 90;
索引已创建。
SQL> create bitmap index t1_i4 on t1(n4) nologging pctfree 90;
索引已创建。
SQL> create index t1_i5 on t1(n5) nologging pctfree 90;
索引已创建。
SQL> create index t1_i6 on t1(n6) nologging pctfree 90;
索引已创建。
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 't1',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 1'
8 );
9 end;
10 /
PL/SQL 过程已成功完成。
这个表中有6个重要的列,没一个列都建立了索引。列n1和n2都有20个不同的值,但是,列n1的值是平均分布在表中;列n2某个值对应的行集群在500个集群中。
列n3和n4和上面两列类似,只不过是存在25个不同的值。
列n5和n6与列n3和n4相同,只不过上面建立的是B树索引。
索引的统计信息如下:
SQL> run
1 select i.index_name,
2 i.blevel,
3 i.leaf_blocks,
4 i.distinct_keys,
5 i.num_rows,
6 i.clustering_factor,
7 i.avg_leaf_blocks_per_key,
8 i.avg_data_blocks_per_key
9* from user_indexes i
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
--------------- ---------- ----------- ------------- ---------- ----------------- ----------------------- -----------------------
T1_I1 1 60 20 139 139 3 6
T1_I2 1 10 20 20 20 1 1
T1_I3 1 63 25 125 125 2 5
T1_I4 1 9 25 25 25 1 1
T1_I5 1 217 25 10000 10000 8 400
T1_I6 1 217 25 10000 1114 8 44
已选择6行。
需要注意到以下几点:
a、数据的集群程度能够在很大程度上影响位图索引中叶块的数目(n1中的数据的散布的,索引有60个叶块;n2是集群的,索引有10个叶块;类似的,n3和n4分别有63和9个叶块)。总的来说,散布数据上的位图索引一般要比集群且相似的数据上要大。但是,B树索引就不会受到这种因素的影响(n5为散步数据,n6为集群数据,但是它们的索引都是217个叶块)。
b、位图索引的CLUSTERING_FACTOR仅仅是NUM_ROWS的一个副本。CLUSTERING_FACTOR的大小和表中数据的散步程度没有直接的联系。
c、对于位图索引来说,某些统计信息的含义将发生变化(尤其是CLUSTERING_FACTOR)
SQL> select /*+ index(t1) */ small_vc from t1 where n5 = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1385878225
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 409 (0)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 400 | 5600 | 409 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | T1_I5 | 400 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N5"=2)
SQL> select /*+ index(t1) */ small_vc from t1 where n6 = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1134273289
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 400 | 5600 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I6 | 400 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N6"=2)
可以看见,针对B树索引下的列的查询对于集群数据和散布数据的执行计划是不同的(54和409)。
SQL> select /*+ index(t1) */ small_vc from t1 where n3 = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1683756096
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 20107 (1)| 00:04:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 400 | 5600 | 20107 (1)| 00:04:02 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | T1_I3 | | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N3"=2)
SQL> select /*+ index(t1) */ small_vc from t1 where n4 = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1419429417
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 5600 | 20105 (1)| 00:04:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 400 | 5600 | 20105 (1)| 00:04:02 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | T1_I4 | | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N4"=2)
可以看见,针对位图索引查询的成本接近一致,不管这些数据是极度散布还是高度集中(20107和20105)。注意,优化器没有报告位图索引的成本。