liudaoru
- 浏览: 1578829 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
nich002：
原网站失效了。撸主简单粗暴的复制过来，可读性极差！差评！
Apache配置详解(最好的APACHE配置教程) -
107x：
不错，谢谢！
LINUX下查看文件夹下的文件个数! -
Hypereo：
好你妹，连个格式都没有！
Apache配置详解(最好的APACHE配置教程) -
resteater：
代码排版感觉有点乱！收发信息代码可读性不强！请问第一次发服务器 ...
java socket例子 -
resteater：
代码排版感觉有点乱！收发信息代码可读性不强！请问第一次发服务器 ...
java socket例子
From: http://www.oracle.com/technology/global/cn/pub/articles/sharma_indexes.html
了解每个索引的正确应用对性能会有很大影响。
一般认为，位图索引最适合于具有低相异值的列（如 GENDER、MARITAL_STATUS 和 RELATION）。然合，这种观点并不完全正确。实际上，对于那些并不经常由许多并行系统更新其数据的系统来说，总是建议采用位图索引。事实上，正如我此处所阐明的，在具有 100% 唯一值（主键的列候选键）的列上的位图索引与 B-tree 索引同样有效。
在本文中，我将提供一些示例和优化程序决策，它们对于低基数列和高基数列上的索引类型是通用的。这些示例将帮助 DBA 了解位图索引的使用实际上并不依赖于基数，而是依赖于应用程序。
比较索引
在唯一列上使用位图索引（ Oracle 不建议这种方法）有几个缺点，其中一个对充足空间的需求。然而，位图索引的大小依赖于列的基数，位图索引是在该列上创建,同时进行数据分配。因此，GENDER 列上的位图索引将小于相同列上的 B-tree 索引。相反，EMPNO（主键的候选键）上的位图索引将比此列上的 B-tree 索引大得多。但是因为访问决策支持系统 (DSS) 的用户比访问交易处理 (OLTP) 系统的用户要少，所以资源对于这些应用程序不是问题。
为演示这一点，我创建了两个表 TEST_NORMAL 和 TEST_RANDOM。我使用 PL/SQL 块在 TEST_NORMAL 表中插入一百万行，然后随机在 TEST_RANDOM 表中插入这些行：
Create table test_normal (empno number(10), ename varchar2(30), sal number(10));
Begin
For i in 1..1000000
Loop
Insert into test_normal
values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
If mod(i, 10000) = 0 then
Commit;
End if;
End loop;
End;
/
Create table test_random
as
select /*+ append */ * from test_normal order by dbms_random.random;
SQL> select count(*) "Total Rows" from test_normal;
Total Rows
----------
1000000
Elapsed: 00:00:01.09
SQL> select count(distinct empno) "Distinct Values" from test_normal;
Distinct Values
---------------
1000000
Elapsed: 00:00:06.09
SQL> select count(*) "Total Rows" from test_random;
Total Rows
----------
1000000
Elapsed: 00:00:03.05
SQL> select count(distinct empno) "Distinct Values" from test_random;
Distinct Values
---------------
1000000
Elapsed: 00:00:12.07
注意 TEST_NORMAL 是条理的，而 TEST_RANDOM 表是随机创建的，因此 TEST_RANDOM 表的数据是无条理的。在上面的表中，列 EMPNO 包含完全相异的值，是很好的一个候选主键。如果您将此列定为主键，则您将创建一个 B-tree 索引而非位图索引，因为 Oracle 不支持位图主键索引。
要分析这些索引的行为，我们将执行下列步骤：
- 在 TEST_NORMAL 上：
- 在 EMPNO 列上创建一个位图索引，并使用等式谓词执行某些查询。
- 在 EMPNO 列上创建一个 B-tree 索引，使用等式谓词执行某些查询，并比较由查询做出的逻辑和物理输入/输出，以取出不同值集的结果。
- 在 TEST_RANDOM 上：
- 与步骤 1A 相同。
- 与步骤 1B 相同。
- 在 TEST_NORMAL 上：
- 与步骤 1A 相同，除了查询是使用在一定范围的谓词执行之外。
- 与步骤 1B 相同，除了查询是使用一定范围的谓词执行之外。现在比较统计值。
- 在 TEST_RANDOM 上：
- 与步骤 3A 相同。
- 与步骤 3B 相同。
- 在 TEST_NORMAL 上：
- 在 SAL 列上创建一个位图索引，然后使用等式谓词执行某些查询，及使用范围谓词执行某些查询。
- 在 SAL 列上创建一个 B-tree 索引，然后使用等式谓词执行某些查询，和使用范围谓词（与步骤 5A 中相同的某些值集）执行某些查询。比较由查询完成的输入/输出以取出结果。
- 将 GENDER 列添加到全部两个表中，并使用三个可能值更新列：M 代表男，F 代表女，null 代表不适用。基于一定条件使用这些值对列进行更新。
- 在此列上创建一个位图索引，并使用等式谓词执行某些查询。
- 在 GENDER 列上创建一个 B-tree 索引，并使用等式谓词执行某些查询。与步骤 7 的结果比较。
步骤 1 到步骤 4 涉及一个高基数（100% 不同）列，步骤 5 涉及一个普通基数列，而步骤 7 和步骤 8 涉及一个低基数列。
步骤 1A（在 TEST_NORMAL 上）
在此步骤中，我们将在 TEST_NORMAL 表上创建一个位图索引，然后检查此索引的大小、其群集因子和表的大小。然后我们将使用等式谓词运行某些查询，并使用此位图索引注明这些查询的输入/输出。
SQL> create bitmap index normal_empno_bmx on test_normal(empno);
Index created.
Elapsed: 00:00:29.06
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Elapsed: 00:00:19.01
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3* where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_BMX');
SEGMENT_NAME Size in MB
------------------------------------ ---------------
TEST_NORMAL 50
NORMAL_EMPNO_BMX 28
Elapsed: 00:00:02.00
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
------------------------------ ---------------------------------
NORMAL_EMPNO_BMX 1000000
Elapsed: 00:00:00.00
您会看到在前面表中索引的大小是 28 MB，而群集因子等于表中的行数。现在让我们使用等式谓词针对不同值集执行查询：
SQL> set autotrace only
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old 1:select * from test_normal where empno=&empno
new 1:select * from test_normal where empno=1000
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
d=1 Bytes=34)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_EMPNO_BMX'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
步骤 1B（在 TEST_NORMAL 上）
现在我们将丢弃此位图索引并在 EMPNO 列上创建一个 B-tree 索引。同以前一样，我们将检查索引的大小及其群集因子并针对相同的值集执行相同的查询，以比较输入/输出。
SQL> drop index NORMAL_EMPNO_BMX;
Index dropped.
SQL> create index normal_empno_idx on test_normal(empno);
Index created.
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_IDX');
SEGMENT_NAME Size in MB
---------------------------------- ---------------
TEST_NORMAL 50
NORMAL_EMPNO_IDX 18
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
---------------------------------- ----------------------------------
NORMAL_EMPNO_IDX 6210
很清楚在此表中，B-tree 索引比 EMPNO 列上的位图索引更小。B-tree 索引的群集因子更加接近表中的块数；由于这一原因，B-tree 索引对于范围谓词查询效率更佳。
现在我们将使用我们的 B-tree 索引针对相同的值集运行相同的查询。
SQL> set autot trace
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old 1:select * from test_normal where empno=&empno
new 1:select * from test_normal where empno=1000
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
d=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (C
ost=3 Card=1)
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
正如您可以看到的那样，针对不同的值集执行查询时，对于在完全唯一列上的位图和 B-tree 索引，一致性读请求和物理读取的数量是相同的。
| 位图 | EMPNO | B-TREE | ||
| 一致性读数 | 物理读数 | 一致性读数 | 物理读数 | |
| 5 | 0 | 1000 | 5 | 0 |
| 5 | 2 | 2398 | 5 | 2 |
| 5 | 2 | 8545 | 5 | 2 |
| 5 | 2 | 98008 | 5 | 2 |
| 5 | 2 | 85342 | 5 | 2 |
| 5 | 2 | 128444 | 5 | 2 |
| 5 | 2 | 858 | 5 | 2 |
步骤 2A（在 TEST_RANDOM 上）
现在我们将在 TEST_RANDOM 上执行相同的实验：
SQL> create bitmap index random_empno_bmx on test_random(empno);
Index created.
SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3* where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_BMX');
SEGMENT_NAME Size in MB
------------------------------------ ---------------
TEST_RANDOM 50
RANDOM_EMPNO_BMX 28
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
------------------------------ ---------------------------------
RANDOM_EMPNO_BMX 1000000
再次，统计值（大小和群集因子）对于 TEST_NORMAL 表上的那些索引是相同的。
SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old 1:select * from test_random where empno=&empno
new 1:select * from test_random where empno=1000
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'RANDOM_EMPNO_BMX'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
步骤 2B（在 TEST_RANDOM 上）
现在，与步骤 1B 中一样，我们将丢弃该位图索引并在 EMPNO 列上创建一个 B-tree 索引。
SQL> drop index RANDOM_EMPNO_BMX;
Index dropped.
SQL> create index random_empno_idx on test_random(empno);
Index created.
SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_IDX');
SEGMENT_NAME Size in MB
---------------------------------- ---------------
TEST_RANDOM 50
RANDOM_EMPNO_IDX 18
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
---------------------------------- ----------------------------------
RANDOM_EMPNO_IDX 999830
此表显示此索引的大小等于其在 TEST_NORMAL 表上的大小，但群集因子更加接近行数，这使得此索引对于范围谓词查询无效（我们将在步骤 4 中看到这种情况）。此群集因子将不会影响等式谓词查询，因为行具有全异的值，且每个键的行数为 1。
现在让我们来运行具有等式谓词和相同值集的查询。
SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old 1:select * from test_random where empno=&empno
new 1:select * from test_random where empno=1000
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_IDX' (NON-UNIQUE) (Cost=3 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
同样，结果几乎与步骤 1A 和 1B 中的的结果一样。数据分配不影响唯一列的一致性读请求和物理读取的数量。
步骤 3A（在 TEST_NORMAL 上）
在这一步中，我们将创建位图索引（与步骤 1A 相似）。我们知道索引的大小和群集因子，它等于表中的行数。现在让我们使用范围谓词运行某些查询。
SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1:select * from test_normal where empno between &range1 and &range2
new 1:select * from test_normal where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=451 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=451 Card=2299 Bytes=78166)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_BMX'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
331 consistent gets
0 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
步骤 3B（在 TEST_NORMAL 上）
在这一步中，我们将对具有 B-tree 索引的 TEST_NORMAL 表执行查询。
SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1:select * from test_normal where empno between &range1 and &range2
new 1:select * from test_normal where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=23 Card=2299 Bytes=78166)
2 1 INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (Cost=8 Card=2299)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
329 consistent gets
15 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
为不同的范围集执行这些查询时，结果会显示如下：
| 位图 | EMPNO（范围） | B-TREE | ||
| 一致读数 | 物理读数 | 一致读数 | 物理读数 | |
| 331 | 0 | 1-2300 | 329 | 0 |
| 285 | 0 | 8-1980 | 283 | 0 |
| 346 | 19 | 1850-4250 | 344 | 16 |
| 427 | 31 | 28888-31850 | 424 | 28 |
| 371 | 27 | 82900-85478 | 367 | 23 |
| 2157 | 149 | 984888-1000000 | 2139 | 35 |
您会发现，这一次两个索引的一致读数和物理读数又几乎一样。最后一个范围 (984888-1000000) 几乎返回 15,000 行，这是针对上面给出的所有范围所取得的行的最大数量。因此，我们请求了全表扫描（通过给出提示 /*+ full(test_normal) */），一致性读数和物理读数的分别为 7,239 和 5,663。
步骤 4A（在 TEST_RANDOM 上）
在这一步中，我们将在具有位图索引的 TEST_RANDOM 表上使用范围谓词运行查询，并检查一致性读数和物理读数。此处您将看到群集因子的影响。
SQL>select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1:select * from test_random where empno between &range1 and &range2
new 1:select * from test_random where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:08.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=453 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=453 Card=2299 Bytes=78166)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_BMX'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2463 consistent gets
1200 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
步骤 4B（在 TEST_RANDOM 上）
在这一步中，我们将具有 B-tree 索引的 TEST_RANDOM 上执行范围谓词查询。记住，此索引的群集因子与表中的行数非常接近（且因而无效）。此处是优化程序关于这一点的建议：
SQL> select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1:select * from test_random where empno between &range1 and &range2
new 1:select * from test_random where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:03.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=613 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (FULL) OF 'TEST_RANDOM' (Cost=613 Card=2299 Bytes=78166)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6415 consistent gets
4910 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
此优化程序选择全表扫描，而不是使用索引，原因是群集因子：
| 位图 | EMPNO（范围） | B-TREE | ||
| 一致性读数 | 物理读数 | 一致性读数 | 物理读数 | |
| 2463 | 1200 | 1-2300 | 6415 | 4910 |
| 2114 | 31 | 8-1980 | 6389 | 4910 |
| 2572 | 1135 | 1850-4250 | 6418 | 4909 |
| 3173 | 1620 | 28888-31850 | 6456 | 4909 |
| 2762 | 1358 | 82900-85478 | 6431 | 4909 |
| 7254 | 3329 | 984888-1000000 | 7254 | 4909 |
仅对于最后的范围 (984888-1000000)，优化程序选择了位图索引的全表扫描，然而，对于所有范围，它选择 B-tree 索引的全表扫描。群集因子是导致这一差异的原因：使用位图索引生成执行计划时，该优化程序不考虑群集因子的值，然而，对于 B-tree 索引，它会考虑群集因子的值。在这种情况下，位图索引执行会比 B-tree 索引更加有效。
下面的步骤列举关于这些索引的值得关注的更多情况。
步骤 5A（在 TEST_NORMAL 上）
在 TEST_NORMAL 表的 SAL 列上创建位图索引。此列具有正常的基数。
SQL> create bitmap index normal_sal_bmx on test_normal(sal);
Index created.
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
Table analyzed.
现在让我们来获取索引的大小和群集因子。
SQL>select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2* from user_segments
3* where segment_name in ('TEST_NORMAL','NORMAL_SAL_BMX');
SEGMENT_NAME Size in MB
------------------------------ --------------
TEST_NORMAL 50
NORMAL_SAL_BMX 4
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
------------------------------ ----------------------------------
NORMAL_SAL_BMX 6001
现在开始进行查询，首先使用等式谓词运行它们：
SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old 1:select * from test_normal where sal=&sal
new 1:select * from test_normal where sal=1869
164 rows selected.
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=168 Bytes=4032)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=39 Card=168 Bytes=4032)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
165 consistent gets
0 physical reads
0 redo size
8461 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
164 rows processed
然后使用范围谓词：
SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old 1:select * from test_normal where sal between &sal1 and &sal2
new 1:select * from test_normal where sal between 1500 and 2000
83743 rows selected.
Elapsed: 00:00:05.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
=2001024)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
Bytes=2001024)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11778 consistent gets
5850 physical reads
0 redo size
4123553 bytes sent via SQL*Net to client
61901 bytes received via SQL*Net from client
5584 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
83743 rows processed
现在丢弃位图索引，并在 TEST_NORMAL 上创建一个 B-tree 索引。
SQL> create index normal_sal_idx on test_normal(sal);
Index created.
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
Table analyzed.
查看索引和群集因子的大小。
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_SAL_IDX');
SEGMENT_NAME Size in MB
------------------------------ ---------------
TEST_NORMAL 50
NORMAL_SAL_IDX 17
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
------------------------------ ----------------------------------
NORMAL_SAL_IDX 986778
在上面的表中，您可以看到此索引比相同列上的位图索引大。群集因子也接近此表中的行数。
下面要进行测试了，首先使用等式谓词：
SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old 1:select * from test_normal where sal=&sal
new 1:select * from test_normal where sal=1869
164 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=169 Card=168 Bytes=4032)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=169 Card=168 Bytes=4032)
2 1 INDEX (RANGE SCAN) OF 'NORMAL_SAL_IDX' (NON-UNIQUE) (Cost=3 Card=168)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
177 consistent gets
0 physical reads
0 redo size
8461 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
164 rows processed
然后，范围谓词：
SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old 1:select * from test_normal where sal between &sal1 and &sal2
new 1:select * from test_normal where sal between 1500 and 2000
83743 rows selected.
Elapsed: 00:00:04.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
=2001024)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
Bytes=2001024)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11778 consistent gets
3891 physical reads
0 redo size
4123553 bytes sent via SQL*Net to client
61901 bytes received via SQL*Net from client
5584 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
83743 rows processed
针对不同的值集执行查询时，输出结果（如下表中所示）揭示一致性读数和物理读数是相同的。
| 位图 |
SAL（等式）
|
B-TREE | 取出的行数 | ||
| 一致性读数 | 物理读数 | 一致性读数 | 物理读数 | ||
| 165 | 0 | 1869 | 177 | 164 | |
| 169 | 163 | 3548 | 181 | 167 | |
| 174 | 166 | 6500 | 187 | 172 | |
| 75 | 69 | 7000 | 81 | 73 | |
| 177 | 163 | 2500 | 190 | 175 |
| 位图 |
SAL（范围）
|
B-TREE | 取出的行数 | ||
| 一致性读数 | 物理读数 | 一致性读数 | 物理读数 | ||
| 11778 | 5850 | 1500-2000 | 11778 | 3891 | 83743 |
| 11765 | 5468 | 2000-2500 | 11765 | 3879 | 83328 |
| 11753 | 5471 | 2500-3000 | 11753 | 3884 | 83318 |
| 17309 | 5472 | 3000-4000 | 17309 | 3892 | 166999 |
| 39398 | 5454 | 4000-7000 | 39398 | 3973 | 500520 |
对于范围谓词，优化程序选择为所有的不同值集进行全表扫描（它根本不使用索引）然而，对于等式谓词，该优化程序使用索引。和以前一样，一致性读数和物理读数是相同的。
因此，您可以得出结论：对于一般基数列，优化程序针对两种类型索引做出的决策都是相同的，且在输入/输出之间不存在显著的差异。
步骤 6（添加 GENDER 列）
在低基数列上执行测试之前，让我们将一个 GENDER 列添加到此表中，并使用 M、F 和 null 值更新它。
SQL> alter table test_normal add GENDER varchar2(1);
Table altered.
SQL> select GENDER, count(*) from test_normal group by GENDER;
S COUNT(*)
- ----------
F 333769
M 499921
166310
3 rows selected.
此列上的位图索引的大小大约是 570 KB，如下表中所示：
SQL> create bitmap index normal_GENDER_bmx on test_normal(GENDER);
Index created.
Elapsed: 00:00:02.08
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_GENDER_BMX');
SEGMENT_NAME Size in MB
------------------------------ ---------------
TEST_NORMAL 50
NORMAL_GENDER_BMX .5625
2 rows selected.
相反，此列上的 B-tree 索引的大小为 13 MB，它比此列上的位图索引要大得多。
SQL> create index normal_GENDER_idx on test_normal(GENDER);
Index created.
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_GENDER_IDX');
SEGMENT_NAME Size in MB
------------------------------ ---------------
TEST_NORMAL 50
NORMAL_GENDER_IDX 13
2 rows selected.
现在，如果我们使用等式谓词执行查询，则优化程序将不利用此索引，不管它是位图索引或 B-tree 索引。它将选择完全表扫描。
SQL> select * from test_normal where GENDER is null;
166310 rows selected.
Elapsed: 00:00:06.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=166310 Bytes=4157750)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=166310 Bytes=4157750)
SQL> select * from test_normal where GENDER='M';
499921 rows selected.
Elapsed: 00:00:16.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=499921 Bytes=12498025)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=499921Bytes=12498025)
SQL>select * from test_normal where GENDER='F'
/
333769 rows selected.
Elapsed: 00:00:12.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=333769 Byte
s=8344225)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=333769
Bytes=8344225)
结论
现在我们已经了解优化程序是如何对这些技术作出反应的，接下来让我们分析一种情况，它将清楚地演示位图索引和 B-tree 索引各自的最佳应用。
在 GENDER 列已有位图索引，在 SAL 列上创建另一个位图索引，然后执行某些查询。将针对这些带 B-tree 索引的列重新执行这些查询。
从 TEST_NORMAL 表中，您需要得到月工资为下列任何值的所有男性雇员的数量：
1000 1500 2000 2500 3000 3500 4000 4500
执行：
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';
这是一个典型的数据仓库查询，当然，您应该永远都不会在 OLTP 系统执行该查询。下面是在两个列上都有位图索引时得到的结果：
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';
1453 rows selected.
Elapsed: 00:00:02.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=198 Card=754 Bytes=18850)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=198 Card=754 Bytes=18850)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP OR
5 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
6 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
7 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
8 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
9 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
10 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
11 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
12 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
13 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
14 3 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_GENDER_BMX'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1353 consistent gets
920 physical reads
0 redo size
75604 bytes sent via SQL*Net to client
1555 bytes received via SQL*Net from client
98 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1453 rows processed
为 B-tree 索引时：
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';
1453 rows selected.
Elapsed: 00:00:03.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=754 Bytes=18850)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=754 Bytes=18850)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6333 consistent gets
4412 physical reads
0 redo size
75604 bytes sent via SQL*Net to client
1555 bytes received via SQL*Net from client
98 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1453 rows processed
正如您此处可以看到的那样，使用 B-tree 索引，优化程序选择了全表扫描，然而在位图索引情形中，它使用索引来响应该查询。您可以根据取出结果所需的输入/输出数量来判断性能高低。
总之，不管基数如何，位图索引最适合于 DSS，原因如下：
- 使用位图索引，优化程序可以有效地响应包括 AND、OR 或 XOR 的查询。（Oracle 支持 B-tree 到位图的动态转换，但它可能效率很低。）
- 使用位图，在搜索空值或对其计数时，优化程序可以响应查询。位图索引也可以索引空值（与 B-tree 索引不同）。
- 最重要的是，DSS 系统中的位图索引支持即席查询，然而，B-tree 索引不支持即席查询。更为特殊的是，如果您有一个具有 50 列的表，且用户经常查询它们中的 10 个（查询全部 10 个或有时查询其中一个），则创建 B-tree 索引将非常困难。如果在所有这些列上创建 10 个位图索引，则所有查询都可以由这些索引来响应，而不管针对全部 10 列的查询，或其中 4 或 6 列的查询，或者是单个列上的查询。AND_EQUAL 提示为 B-tree 索引提供此功能，但一个查询仅能针对少于五个索引。使用位图索引将不会有这一限制。
但 B-tree 索引更适合于 OLTP 应用程序，在该应用程序中用户的查询是相对固定的（并在生产部署之前进行过很好的调整），这与即席查询相反，这些相对固定的查询出现频率更低，且在非高峰工作时间执行。因为数据在 OLTP 应用程序中经常更新，并已从 OLTP 应用程序中删除，所以在这些情形下，位图索引会导致严重的死锁问题。
本文提供的数据能让你清楚的了解各种情况。这两个索引都具有一个相似的目的：尽快地返回结果。但您选择使用哪个索引应该完全取决于应用程序的类型，而不是取决于基数的级别。
Vivek Sharma（vivek.l.sharma@accenture.com 或 vlsharma@hotmail.com）是孟买 Accenture India 公司的高级 Oracle DBA。他有六年的 Oracle 技术应用经验，且是 Oracle 认证专家。Vivek 擅长于性能调整和 SQL-PL/SQL 优化。
分享到：
评论
1 楼
liudaoru
2008-05-06
http://www.itpub.net/archiver/tid-723511.html
BitMap索引的原理
---------------------------------
state_code bitmaps:
DE: <111111111000000000>
NH: <000000000111111111>
在上面的程序清单中，每一个数字代表EMPLOYEE表中的一个行。由于这里只考虑10行，
所以只有10个位映射值示出来。通过读取State_Code的位映射，前5个记录的值为“DE”(用1
表示)，后5个记录的值则不为“DE”(用0表示)。对这个列可能有更多的值。在这种情况下，
对应每一个值都将有一个独立的位映射条目。
Oracle优化程序能够在查询进程中动态地将位映射索引内容转换为RowID。
[注意:]BITMAP INDEX通常用于DSS,如果你的系统是OLTP,DML操作将LOCK整个BITMAP SEGMENT,
因此只在DSS下 考虑BITMAP INDEX
BitMap索引的原理
---------------------------------
state_code bitmaps:
DE: <111111111000000000>
NH: <000000000111111111>
在上面的程序清单中，每一个数字代表EMPLOYEE表中的一个行。由于这里只考虑10行，
所以只有10个位映射值示出来。通过读取State_Code的位映射，前5个记录的值为“DE”(用1
表示)，后5个记录的值则不为“DE”(用0表示)。对这个列可能有更多的值。在这种情况下，
对应每一个值都将有一个独立的位映射条目。
Oracle优化程序能够在查询进程中动态地将位映射索引内容转换为RowID。
[注意:]BITMAP INDEX通常用于DSS,如果你的系统是OLTP,DML操作将LOCK整个BITMAP SEGMENT,
因此只在DSS下 考虑BITMAP INDEX
发表评论
-
mysql知识细点
2010-10-19 13:27 1253From：http://dev.mysql.com/tech- ... -
FEDERATED存储引擎的描述--MySql数据库[z]
2010-09-27 16:58 1489From: http://www.wang48.com/jis ... -
mysql udf
2010-08-23 15:48 983User Defined Function (UDF) ... -
Mysql监控指南【z】
2010-06-22 10:12 1403Mysql作为广泛应用的数据库系统，平时运维工作中对她的监 ... -
mysql 主从配置 [z]
2010-06-03 17:41 1548From: http://blog.csdn.net/php ... -
mysql主从复制,读写分离
2010-03-17 13:51 2268From：http://hi.baidu.com/yzy888 ... -
MySQL中ALTER TABLE时的性能问题
2010-03-15 16:51 5593From: http://www.evget.com/zh-C ... -
在线调整大数据表的结构的方法
2010-03-09 09:46 1762我们经常会遇到修改一个大数据表的需求，不如给一个大数据表增加字 ... -
利用mysqlbinlog工具恢复MySQL数据库【z】
2010-02-23 16:08 2380From：http://www.phpv.net/html ... -
MySQL性能优化的最佳20+条经验
2010-01-25 16:47 4568From：http://www.uml.org.cn/sjjm ... -
分布式key-value存储方案 Cassandra
2009-12-17 12:03 2776参考：http://www.oschina.net/p/cas ... -
mysql优化 mysql性能调优
2009-11-23 08:09 3786From： http://www.phpcms.cn/2009 ... -
MYSQL的MERGE存储引擎
2009-09-26 12:31 1729From：http://blog.csdn.net/qiuye ... -
MySQL索引分析和优化
2009-09-21 10:31 914From：http://tech.it168.com/a200 ... -
[转贴] 迅速优化MySQL数据库性能
2009-07-03 23:14 1260From: http://tech.techweb.com. ... -
mysqldump导入办法
2009-05-25 13:19 23261From：http://www.linuxidc.com/Li ... -
mysql根据一个表的内容更新另一个表
2009-05-12 15:40 1880update university a,temp_colle ... -
透视MySQL数据库之更新语句
2009-04-18 13:36 1555From: http://tech.techweb.com.c ... -
优化MySQL数据库性能的八种方法
2009-03-26 15:21 975From: http://www.blogjava.net/ ... -
mysql读写分离
2009-03-02 17:30 2435From：http://blog.csdn.net/yue ...
相关推荐
位图索引与B树（B-tree）索引是数据库管理系统中常见的两种索引结构，它们各自具有不同的特性和适用场景。在理解这两种索引之前，我们先来了解一下索引的基本概念。 索引是数据库为了加速数据检索而创建的一种数据...
现在，我们知道优化器如何对这些技术做出反应，清楚地说明 bitmap 索引和 B-tree 索引各自的最好应用。 在 GENDER 列适当地带一个 bitmap 索引，在 SAL 列上创建另外一个位图索引，然后执行一些查询。在这些列上，用...
### B-树索引与位图索引的深入解析 #### B-树索引概述 B-树索引是Oracle数据库中最常用的索引类型之一，它利用B-树的数据结构来组织索引项，以便快速查找数据。B-树是一种自平衡的树形数据结构，每个节点最多可以...
内容概要：本文详细介绍了 Oracle中的各种索引类型及其使用场景，包括 B*Tree索引、位图索引、索引组织表、降序索引、反向键索引和基于函数的索引。每种索引的优缺点、适用场合和创建方法均有详细介绍。文章还讨论了...
为了进行位图索引与B*树索引的对比实验，首先需要创建一个包含大量数据的测试表。这里通过`dba_objects`表作为数据源来构建测试表`leo_bm_t1`，然后对该表进行了两次复制插入操作，以确保表中有足够的数据量用于后续...
B树与哈希索引相比，在范围查询和有序数据处理方面具有优势。 数据结构和算法部分详细介绍了不同类型的B树，例如可变长度记录、规范化键、前缀B树和CPU缓存。其中，可变长度记录的B树支持动态记录大小，规范化键...
- `Attach`：此函数用于将一个DIB（设备无关位图）或DDB（设备依赖位图）与CImage对象关联，使得CImage可以对位图进行操作。 - `Detach`：断开CImage对象与位图的关联，让位图与CImage分离。 - `Create`：创建一...
BitCube的索引设计依据数据特性，为一般维度和高基维度分别设计了标准位图和高基位图索引，同时针对数值类型的数据采用了数值位图索引，以支持范围查询和实时聚合。这样的设计能够灵活应对不同场景的需求，提高数据...
- 索引的概念：理解索引的作用和类型，如B树索引、位图索引等。 - 创建和管理索引：如何创建、查看和删除索引。 - 索引的选择性：理解索引对查询性能的影响，以及如何选择合适的索引策略。 6. **视图** - 视图...
- 索引概述：用于提高数据检索的性能，常见的索引类型包括B-树索引、位图索引和基于函数的索引。 - 列和行：表中的数据可以按列和行的形式组织，其中Oracle支持列式存储。 - 示例：CREATE TABLE和ALTER TABLE语句...
此外，基于规则的优化器不会考虑位图索引，这意味着Oracle在执行SQL前可能不会选择使用位图索引。 第四，优化位图索引的策略还包括合理规划索引的维护。由于位图索引在数据更新时可能需要更多的维护工作，比如插入...
- 索引类型：B树索引、位图索引等。 4. **性能调优** - SQL调优：通过分析执行计划来优化查询语句。 - 内存调优：调整SGA、PGA等内存结构大小。 - 日志与备份策略：定期备份数据，确保数据安全。 #### 三、...
Oracle 数据库的位图索引原理与应用 Oracle 数据库中的位图索引是一种特殊类型的索引，主要用于解决查询优化问题。 在实际应用中，列值重复率高的情况非常常见，使用 B 树索引效果不佳。在这种情况下，位图索引就成...
#### 一、概览与介绍 **PIL（Python Imaging Library）** 是一个广泛使用的Python库，用于处理图像数据。它提供了丰富的功能来处理各种图像格式，包括创建、编辑和保存图像。该中文手册是基于PIL 1.1.5版本编写的，...
Oracle数据库中的索引是提升查询性能的关键工具，它与书籍的目录类似，帮助快速定位到所需数据。在Oracle中，索引并非强制性，表可以没有索引，但索引的存在能够显著加快数据检索速度，尤其对于大数据量的表。 1. ...
- 8位位图通常用于索引色彩，每个像素对应一个颜色表中的颜色。 - 24位位图使用RGB三原色，每个像素由三个字节表示（BGR顺序）。 - 32位位图增加了透明度通道（Alpha通道），常用于支持透明效果。 通过理解BMP文件...
3. 索引选择与优化： - 索引选择：根据查询模式、数据分布和业务需求选择合适的索引类型。 - 索引维护：定期分析索引的使用情况，优化不必要的索引，避免索引失效。 - 索引覆盖：如果查询仅涉及索引中的列，...