位图索引与 B-tree 索引：选择与时间[z]

最新推荐文章于 2024-10-24 09:44:39 发布
最新推荐文章于 2024-10-24 09:44:39 发布
阅读量120 收藏
点赞数
分类专栏： 数据库技术 文章标签： SQL .net Oracle Access F#

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 不支持位图主键索引。

要分析这些索引的行为，我们将执行下列步骤：

  1. 在 TEST_NORMAL 上：
    1. 在 EMPNO 列上创建一个位图索引，并使用等式谓词执行某些查询。
    2. 在 EMPNO 列上创建一个 B-tree 索引，使用等式谓词执行某些查询，并比较由查询做出的逻辑和物理输入/输出，以取出不同值集的结果。
  2. 在 TEST_RANDOM 上：
    1. 与步骤 1A 相同。
    2. 与步骤 1B 相同。
  3. 在 TEST_NORMAL 上：
    1. 与步骤 1A 相同，除了查询是使用在一定范围的谓词执行之外。
    2. 与步骤 1B 相同，除了查询是使用一定范围的谓词执行之外。现在比较统计值。
  4. 在 TEST_RANDOM 上：
    1. 与步骤 3A 相同。
    2. 与步骤 3B 相同。
  5. 在 TEST_NORMAL 上：
    1. 在 SAL 列上创建一个位图索引，然后使用等式谓词执行某些查询，及使用范围谓词执行某些查询。
    2. 在 SAL 列上创建一个 B-tree 索引，然后使用等式谓词执行某些查询，和使用范围谓词（与步骤 5A 中相同的某些值集）执行某些查询。比较由查询完成的输入/输出以取出结果。
  6. 将 GENDER 列添加到全部两个表中，并使用三个可能值更新列：M 代表男，F 代表女，null 代表不适用。基于一定条件使用这些值对列进行更新。
  7. 在此列上创建一个位图索引，并使用等式谓词执行某些查询。
  8. 在 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 索引，一致性读请求和物理读取的数量是相同的。

位图EMPNOB-TREE
一致性读数物理读数一致性读数物理读数
50100050
52239852
52854552
529800852
528534252
5212844452
5285852

步骤 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
一致读数物理读数一致读数物理读数
33101-23003290
28508-19802830
346191850-425034416
4273128888-3185042428
3712782900-8547836723
2157149984888-1000000213935

您会发现，这一次两个索引的一致读数和物理读数又几乎一样。最后一个范围 (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
一致性读数物理读数一致性读数物理读数
246312001-230064154910
2114318-198063894910
257211351850-425064184909
3173162028888-3185064564909
2762135882900-8547864314909
72543329984888-100000072544909

仅对于最后的范围 (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取出的行数
一致性读数物理读数一致性读数物理读数
16501869177164 
1691633548181167 
1741666500187172 
756970008173 
1771632500190175 

位图
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 优化。

liudaoru
关注
专栏目录
mysql gin索引_Postgresql、MySQL相关的四种索引类型：B-Tree，Hash，Gist，GIN
weixin_39906499的博客
01-20 474
索引是指按表中某些关键属性或表达式建立元组的逻辑顺序，它是由一系列表元组的标识号组成的一个列表。在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的分类按存储结构区分：...
Oracle12文档 索引和索引结构表
QQ1845517170的博客
07-18 1418
因为不免费，又不是自己想要的，不得不强行把官方文档翻译过来。 看了看自己大学还需要重修的英语，留下了愤慨的眼泪。 本文依据oracle12.2，版本不同是否有更新大改等，由于本人凭着微薄的java基础找到工作(全靠学校太好捞我)，见的太少，尚不得知 官方英文文档 索引部分 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/indexes-and-index-organized-tables.html#GUID-797E
B树索引与位图索引
talentluke的专栏
04-02 725
一、 B树索引的缺陷 　　在实际工作中，B树索引是Oracle数据库中最常用的一种索引。如在使用Create Index语句创建索引的时候，默认采用的就是B树索引。在B树索引中，是通过在索引中保存排序过的索引列以及其对应的Rowid列的值来实现的。不过对 于某些比较特殊的情况，如基数比较小的列，使用这个B树索引反而会降低数据库的查询效率。 　　基数在Oracle数据库中指的是某个列可能拥有...
Bitmap位图索引与普通的B-Tree索引锁的比较
sunbocong的博客
05-11 2118
Bitmap位图索引与普通的B-Tree索引锁的比较 通过以下实验，来验证Bitmap位图索引较之普通的B-Tree索引锁的“高昂代价”。位图索引会带来“位图段级锁”，实际使用过程一定要充分了解不同索引带来的锁代价情况。 1.为比较区别，创建两种索引类型的测试表 1）在表t_bitmap上创建位图索引 SYS@ORA11GR2>create table t_bitmap (i...
位图索引与 B-tree 索引：选择与时间
cuixie2370的博客
02-21 61
http://www.oracle.com/technology/global/cn/pub/articles/sharma_indexes.html ...
Oracle 表空间，序列，索引，分区表的应用
miyouli的博客
07-15 1350
Oracle数据库应用 一.表空间 Oracle数据库包含逻辑构造和物理构造。数据库的物理构造是指构造成数据库的一组操作系统文件。数据库的逻辑构造是指描述数据组织方式的一组逻辑概念及它们之间的关系,表空间是数据库逻辑构造的一个重要组件,表空间可以存放各种。 1.表空间分类 永久表空间：存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。 临时表空间：存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的
clfB-tree: Cacheline Friendly Persistent B-tree for NVRAM
Tian_pp的博客
03-14 914
clfB-tree: Cacheline Friendly Persistent B-tree for NVRAM（ACM Trans 2018）
Oracle数据库索引设计与优化：揭秘索引背后的原理和优化技巧，提升数据库查询效率
![Oracle数据库索引设计与优化：揭秘索引背后的原理和优化技巧，提升数据库查询效率]...# 1. Oracle索引基础** Oracle索引是一种数据结构，用于快速查找表中的特定数据。索引包含表中列的值和指向表中相应行的指针。...
B+Tree索引与原理
Hello World
07-17 879
基本概念 上图就是一个带顺序访问指针的B+Tree结构图，它由2部分组成内部节点(Interior Page)，叶节点(Leaf Page)。 其中内部节点只能存储key和指针，真正的数据是存储在叶节点 ...
MySQL——B+Tree、索引及使用时优化
gangye19970101的博客
12-03 1368
MySQL介绍、索引、BTree介绍
B-树索引与位图索引
heyixiang的专栏
09-16 6713
/*刚从书上看到了位图索引，自己经过了测试，于是现学现卖，希望对还不了解的人有所帮助*/ B-树索引在Oracle中是一个通用的索引，在创建索引时它就是默认的索引类型。最多可以包括32列。 位图索引Oracle为每个唯一键创建一个位图，然后把与键值所关联的ROWID保存为位图。最多可以包括30列。 一般情况下，大多数用户都只创建TYPE为NORMAL的B-树索引，所以对于较低基数的列我们都是不创建
移动开发（五）：.NET MAUI中自定义主题设置
IT技术分享社区
10-24 851
性能考虑：如果用户频繁地在APP运行时更改主题可能会导致性能问题，尤其是在APP主题资源字典很大或者包含大量资源的情况下，可能会引起APP运行的卡顿甚至崩溃的情况。今天给大家分享.NET MAUI应用中如何自定义主题，提升APP本身个性化设置的能力，让你开发的APP更具有吸引力。资源冲突：如果你在不同的资源字典中定义了相同键的资源，则后加载的资源字典中的值将覆盖先前的值。注意事项：每个主题文件所包含的键值对 数量要保持一致，避免切换主题的时候找不到对应的值。UserAppTheme属性，下拉可以设置主题。
数据挖掘（三）相关数据集资源
最新发布
10-27
数据挖掘（三）相关数据集资源
《GNU_Linux环境高级编程》.pdf
10-27
《GNU_Linux环境高级编程》.pdf
人工智能-基于kinect的人体动作识别系统源码（毕业设计）
10-27
人工智能-基于kinect的人体动作识别系统源码（毕业设计），该项目是个人毕设项目，答辩评审分达到98分，代码都经过调试测试，确保可以运行！欢迎下载使用，可用于小白学习、进阶。该资源主要针对计算机、通信、人工智能、自动化等相关专业的学生、老师或从业者下载使用，亦可作为期末课程设计、课程大作业、毕业设计等。项目整体具有较高的学习借鉴价值！基础能力强的可以在此基础上修改调整，以实现不同的功能。 人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人工智能-基于kinect的人体动作识别系统源码（毕业设计）人
福建农林大学在陕西2020-2024各专业最低录取分数及位次表.pdf
10-27
那些年，与你同分同位次的同学都去了哪里？全国各大学在陕西2020-2024年各专业最低录取分数及录取位次数据，高考志愿必备参考数据
Oracle 索引详解：B-Tree与Bitmap索引对比
Bitmap索引在存储空间上比B-Tree索引节省，但更新代价高，因为需要更新整个位图。它们适合于只读系统和数据仓库（DSS）中的复杂查询，尤其是涉及多个AND/OR条件的查询。 重建索引是一种维护索引的方法，当索引碎片...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值