Oracle - 怎样使用B树索引和位图索引

原创 2012年03月26日 21:53:56

注:low-cardinality是指该列或者列的组合具有的不同值的个数较少,即该列有很多重复值。high-cardinality是指该列或者列的组合具有不同的值的个数较多,即该列有很少的重复值。

理解每种索引的适用场合将对性能产生重大影响。

传统观念认为位图索引最适用于拥有很少不同值的列 ---- 例如GENDER, MARITAL_STATUS,和RELATION。但是,这种假设是不准确的。实际上,对于大多非频繁更新的并发系统,位图索引也是适用的。事实上,下面将会展示,对以一个具有100%唯一值的列(主键的候选列)来说,位图索引和B树索引一样有效。

本文将提供一些例子以及优化建议,它们对于low-cardinality和high-cardinality上的两种索引都是通用的。这些例子将帮助DBA理解位图索引不是依赖于cardinality而是依赖于程序自身。

索引比较

在唯一列上使用位图索引有一些缺点 --- 其中一个是需要足够的空间(Oracle不推荐使用)。但是,位图索引的大小不但与位图索引列的cardinality有关,还与数据的分布有关。因此,GENDER列上的位图索引比B树索引要小,相反,EMPNO上的位图索引比B树索引大的多。但是,相对于OLTP系统来说,决策支持系统只有很少的用户访问,因而对于这些系统,资源不是问题。

为了阐明这个观点,我创建了两个表,test_normal和test_random。用PL/SQL块在test_normal中插入100万条记录,然后在test_random表中随机插入相同的记录。

  1. Create table test_normal (empno number(10), ename varchar2(30), sal number(10));
  2. Begin
  3. For i in 1..1000000
  4. Loop
  5. Insert into test_normal
  6. values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
  7. If mod(i, 10000) = 0 then
  8. Commit;
  9. End if;
  10. End loop;
  11. End;
  12. /
  13. Create table test_random
  14. as
  15. select /*+ append */ * from test_normal order by dbms_random.random;
  16. SQL> select count(*) "Total Rows" from test_normal;
  17. Total Rows
  18. ----------
  19. 1000000
  20. Elapsed: 00:00:01.09
  21. SQL> select count(distinct empno) "Distinct Values" from test_normal;
  22. Distinct Values
  23. ---------------
  24. 1000000
  25. Elapsed: 00:00:06.09
  26. SQL> select count(*) "Total Rows" from test_random;
  27. Total Rows
  28. ----------
  29. 1000000
  30. Elapsed: 00:00:03.05
  31. SQL> select count(distinct empno) "Distinct Values" from test_random;
  32. Distinct Values
  33. ---------------
  34. 1000000
  35. Elapsed: 00:00:12.07

注意,test_normal表是组织良好的,test_random表是随机创建的,因此,其中的数据是无组织的。在上面的表中,EMPNO列上的值完全不同,因此可以作为候选主键。如果你把该列定义为主键,oracle将会建立一个B树索引,因为Oracle不支持主键位图索引。

为了分析这些索引的行为,我们执行下面的步骤:

  1. 在表test_normal上:
    1. 在EMPNO列上建立一个位图索引,并执行一些相等性查询。
    2. 在EMPNO列上建立一个B树索引,执行一些相等性查询,并且比较获得不同结果集所执行的查询需要的物理I/O和逻辑I/O的次数。
  2. 在表test_random表上:
    1. 和1.1相同的步骤
    2. 和1.2相同的步骤
  3. 在表test_normal上:
    1. 和1.1相同的步骤,但是执行范围查询。
    2. 和1.2相同的步骤,但是执行范围查询。比较统计结果。
  4. 在表test_random表上:
    1. 和3.1相同的步骤。
    2. 和3.2相同的步骤
  5. 在表test_normal上:
    1. 在SAL列上建立一个位图索引,并且执行一些相等性查询和范围查询。
    2. 在SAL列上建立一个B树索引,并且执行一些相等性查询和范围查询(和5.1相同的结果集),比较获取结果执行的I/O次数。
  6. 在两个表中添加GENDER列,并且把该列更新为3个可能的值:M(女性), F(男性), null(未知)。根据一些条件更新该列的值。
  7. 在该列上建立一个位图索引并且执行一些相等性查询。
  8. 在GENDER列上建立一个B树索引并且执行一些相等性查询,和步骤7的结果比较。

步骤1到4涉及一个high-cardinality列(完全不同),步骤5是一个normal-cardinality列,步骤7和8是一个low-cardinality列。

步骤1.1(在表test_normal上)

在该步中,我们在表test_normal上建立一个位图索引,然后检查索引的大小、聚簇因子(clustering factor)和表的大小。然后执行一些相等性查询并且查看使用位图索引时查询需要的I/O次数。

  1. SQL> create bitmap index normal_empno_bmx on test_normal(empno);
  2. Index created.
  3. Elapsed: 00:00:29.06
  4. SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
  5. Table analyzed.
  6. Elapsed: 00:00:19.01
  7. SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  8. 2 from user_segments
  9. 3* where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_BMX');
  10. SEGMENT_NAME Size in MB
  11. ------------------------------------ ---------------
  12. TEST_NORMAL 50
  13. NORMAL_EMPNO_BMX 28
  14. Elapsed: 00:00:02.00
  15. SQL> select index_name, clustering_factor from user_indexes;
  16. INDEX_NAME CLUSTERING_FACTOR
  17. ------------------------------ ---------------------------------
  18. NORMAL_EMPNO_BMX 1000000
  19. Elapsed: 00:00:00.00

可以看到,表上索引的大小是28M并且聚簇因子的大小等于表中的行数。现在我们为不同的结果集执行一些相等性查询:

  1. SQL> set autotrace only
  2. SQL> select * from test_normal where empno=&empno;
  3. Enter value for empno: 1000
  4. old 1: select * from test_normal where empno=&empno
  5. new 1: select * from test_normal where empno=1000
  6. Elapsed: 00:00:00.01
  7. Execution Plan
  8. ----------------------------------------------------------
  9. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
  10. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
  11. d=1 Bytes=34)
  12. 2 1 BITMAP CONVERSION (TO ROWIDS)
  13. 3 2 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_EMPNO_BMX'
  14. Statistics
  15. ----------------------------------------------------------
  16. 0 recursive calls
  17. 0 db block gets
  18. 5 consistent gets
  19. 0 physical reads
  20. 0 redo size
  21. 515 bytes sent via SQL*Net to client
  22. 499 bytes received via SQL*Net from client
  23. 2 SQL*Net roundtrips to/from client
  24. 0 sorts (memory)
  25. 0 sorts (disk)
  26. 1 rows processed

步骤1.2(在表test_normal上)

现在删除表中EMPNO列上的位图索引并创建一个B树索引。像前面一样我们查看索引的大小、聚簇因子的大小并且执行相同的查询,比较I/O的次数。

  1. SQL> drop index NORMAL_EMPNO_BMX;
  2. Index dropped.
  3. SQL> create index normal_empno_idx on test_normal(empno);
  4. Index created.
  5. SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
  6. Table analyzed.
  7. SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  8. 2 from user_segments
  9. 3 where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_IDX');
  10. SEGMENT_NAME Size in MB
  11. ---------------------------------- ---------------
  12. TEST_NORMAL 50
  13. NORMAL_EMPNO_IDX 18
  14. SQL> select index_name, clustering_factor from user_indexes;
  15. INDEX_NAME CLUSTERING_FACTOR
  16. ---------------------------------- ----------------------------------
  17. NORMAL_EMPNO_IDX 6210

很明显,在该表的EMPNO列上,B树索引比位图索引要小。B树索引上的聚簇因子接近于表中的数据块数;因此B树索引对于范围查询更有效。

现在,我们使用B树索引执行相同的查询。

  1. SQL> set autot trace
  2. SQL> select * from test_normal where empno=&empno;
  3. Enter value for empno: 1000
  4. old 1: select * from test_normal where empno=&empno
  5. new 1: select * from test_normal where empno=1000
  6. Elapsed: 00:00:00.01
  7. Execution Plan
  8. ----------------------------------------------------------
  9. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
  10. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
  11. d=1 Bytes=34)
  12. 2 1 INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (C
  13. ost=3 Card=1)
  14. Statistics
  15. ----------------------------------------------------------
  16. 29 recursive calls
  17. 0 db block gets
  18. 5 consistent gets
  19. 0 physical reads
  20. 0 redo size
  21. 515 bytes sent via SQL*Net to client
  22. 499 bytes received via SQL*Net from client
  23. 2 SQL*Net roundtrips to/from client
  24. 0 sorts (memory)
  25. 0 sorts (disk)
  26. 1 rows processed

可以看到,对于相同的结果集,在唯一列上的位图索引和B树索引需要相同的物理和逻辑读取次数。

BITMAP(位图) EMPNO B-TREE(B树)
Consistent Reads Physical Reads Consistent Reads Physical Reads
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

步骤2.1(在表test_random上)

现在,在test_random表上执行相同的操作:

  1. SQL> create bitmap index random_empno_bmx on test_random(empno);
  2. Index created.
  3. SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;
  4. Table analyzed.
  5. SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  6. 2 from user_segments
  7. 3* where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_BMX');
  8. SEGMENT_NAME Size in MB
  9. ------------------------------------ ---------------
  10. TEST_RANDOM 50
  11. RANDOM_EMPNO_BMX 28
  12. SQL> select index_name, clustering_factor from user_indexes;
  13. INDEX_NAME CLUSTERING_FACTOR
  14. ------------------------------ ---------------------------------
  15. RANDOM_EMPNO_BMX 1000000

再次,索引上的统计结果(大小和聚簇因子)和在表test_normal中是相同的:

  1. SQL> select * from test_random where empno=&empno;
  2. Enter value for empno: 1000
  3. old 1: select * from test_random where empno=&empno
  4. new 1: select * from test_random where empno=1000
  5. Elapsed: 00:00:00.01
  6. Execution Plan
  7. ----------------------------------------------------------
  8. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
  9. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
  10. 2 1 BITMAP CONVERSION (TO ROWIDS)
  11. 3 2 BITMAP INDEX (SINGLE VALUE) OF 'RANDOM_EMPNO_BMX'
  12. Statistics
  13. ----------------------------------------------------------
  14. 0 recursive calls
  15. 0 db block gets
  16. 5 consistent gets
  17. 0 physical reads
  18. 0 redo size
  19. 515 bytes sent via SQL*Net to client
  20. 499 bytes received via SQL*Net from client
  21. 2 SQL*Net roundtrips to/from client
  22. 0 sorts (memory)
  23. 0 sorts (disk)
  24. 1 rows processed

步骤2.2(在表test_random上)

现在,和步骤1.2一样,我们删除EMPNO列上的位图索引并且创建一个B树索引。

  1. SQL> drop index RANDOM_EMPNO_BMX;
  2. Index dropped.
  3. SQL> create index random_empno_idx on test_random(empno);
  4. Index created.
  5. SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;
  6. Table analyzed.
  7. SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  8. 2 from user_segments
  9. 3 where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_IDX');
  10. SEGMENT_NAME Size in MB
  11. ---------------------------------- ---------------
  12. TEST_RANDOM 50
  13. RANDOM_EMPNO_IDX 18
  14. SQL> select index_name, clustering_factor from user_indexes;
  15. INDEX_NAME CLUSTERING_FACTOR
  16. ---------------------------------- ----------------------------------
  17. RANDOM_EMPNO_IDX 999830

该表的索引大小和表test_normal是一样的,但是聚簇因子更接近于行数,这就使得该索引对于范围查询不再高效。该聚簇因子不影响相等性查询,因为该列的值是唯一的,每个键对应1行记录。

现在,在相同的结果集上执行相等性查询。

  1. SQL> select * from test_random where empno=&empno;
  2. Enter value for empno: 1000
  3. old 1: select * from test_random where empno=&empno
  4. new 1: select * from test_random where empno=1000
  5. Elapsed: 00:00:00.01
  6. Execution Plan
  7. ----------------------------------------------------------
  8. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
  9. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
  10. 2 1 INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_IDX' (NON-UNIQUE) (Cost=3 Card=1)
  11. Statistics
  12. ----------------------------------------------------------
  13. 0 recursive calls
  14. 0 db block gets
  15. 5 consistent gets
  16. 0 physical reads
  17. 0 redo size
  18. 515 bytes sent via SQL*Net to client
  19. 499 bytes received via SQL*Net from client
  20. 2 SQL*Net roundtrips to/from client
  21. 0 sorts (memory)
  22. 0 sorts (disk)
  23. 1 rows processed

再次表明,结果和步骤1.1和1.2几乎相同。对于唯一列来说,数据分布不影响逻辑和物理I/O。

步骤3.1(在表test_normal上)

在该步中,我们将创建一个位图索引。我们知道索引的聚簇因子大小和表中的行数相同。现在我们执行一些范围查询。

  1. SQL> select * from test_normal where empno between &range1 and &range2;
  2. Enter value for range1: 1
  3. Enter value for range2: 2300
  4. old 1: select * from test_normal where empno between &range1 and &range2
  5. new 1: select * from test_normal where empno between 1 and 2300
  6. 2300 rows selected.
  7. Elapsed: 00:00:00.03
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=451 Card=2299 Bytes=78166)
  11. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=451 Card=2299 Bytes=78166)
  12. 2 1 BITMAP CONVERSION (TO ROWIDS)
  13. 3 2 BITMAP INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_BMX'
  14. Statistics
  15. ----------------------------------------------------------
  16. 0 recursive calls
  17. 0 db block gets
  18. 331 consistent gets
  19. 0 physical reads
  20. 0 redo size
  21. 111416 bytes sent via SQL*Net to client
  22. 2182 bytes received via SQL*Net from client
  23. 155 SQL*Net roundtrips to/from client
  24. 0 sorts (memory)
  25. 0 sorts (disk)
  26. 2300 rows processed

步骤3.2(在表test_normal上)

该步中,我们在test_normal的B树索引上执行查询。

  1. SQL> select * from test_normal where empno between &range1 and &range2;
  2. Enter value for range1: 1
  3. Enter value for range2: 2300
  4. old 1: select * from test_normal where empno between &range1 and &range2
  5. new 1: select * from test_normal where empno between 1 and 2300
  6. 2300 rows selected.
  7. Elapsed: 00:00:00.02
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=2299 Bytes=78166)
  11. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=23 Card=2299 Bytes=78166)
  12. 2 1 INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (Cost=8 Card=2299)
  13. Statistics
  14. ----------------------------------------------------------
  15. 0 recursive calls
  16. 0 db block gets
  17. 329 consistent gets
  18. 15 physical reads
  19. 0 redo size
  20. 111416 bytes sent via SQL*Net to client
  21. 2182 bytes received via SQL*Net from client
  22. 155 SQL*Net roundtrips to/from client
  23. 0 sorts (memory)
  24. 0 sorts (disk)
  25. 2300 rows processed

在不同的范围上执行的查询结果如下:

BITMAP EMPNO (Range) B-TREE
Consistent Reads Physical Reads Consistent Reads Physical Reads
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

可以看到,在两种索引上需要的逻辑和物理IO基本上是相同的。最后一个范围(984888-1000000)差不多返回了15,000行,是所有范围查询中最大的。当我们执行全表扫描时(通过/*+ full(test_normal) */ ),物理和逻辑IO的次数是7239和5663.

步骤4.1(在表test_random上)

在该步中,我们将在表test_random的位图索引上执行范围查询,在这儿,你将看到聚簇因子的影响。

  1. SQL>select * from test_random where empno between &range1 and &range2;
  2. Enter value for range1: 1
  3. Enter value for range2: 2300
  4. old 1: select * from test_random where empno between &range1 and &range2
  5. new 1: select * from test_random where empno between 1 and 2300
  6. 2300 rows selected.
  7. Elapsed: 00:00:08.01
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=453 Card=2299 Bytes=78166)
  11. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=453 Card=2299 Bytes=78166)
  12. 2 1 BITMAP CONVERSION (TO ROWIDS)
  13. 3 2 BITMAP INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_BMX'
  14. Statistics
  15. ----------------------------------------------------------
  16. 0 recursive calls
  17. 0 db block gets
  18. 2463 consistent gets
  19. 1200 physical reads
  20. 0 redo size
  21. 111416 bytes sent via SQL*Net to client
  22. 2182 bytes received via SQL*Net from client
  23. 155 SQL*Net roundtrips to/from client
  24. 0 sorts (memory)
  25. 0 sorts (disk)
  26. 2300 rows processed

步骤4.2(在表test_random上)

在该步中,我们将在test_random的B树索引上执行范围查询。回想一下,该索引上的聚簇因子接近于表中记录的行数。下面是优化器的输出:

  1. SQL> select * from test_random where empno between &range1 and &range2;
  2. Enter value for range1: 1
  3. Enter value for range2: 2300
  4. old 1: select * from test_random where empno between &range1 and &range2
  5. new 1: select * from test_random where empno between 1 and 2300
  6. 2300 rows selected.
  7. Elapsed: 00:00:03.04
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=613 Card=2299 Bytes=78166)
  11. 1 0 TABLE ACCESS (FULL) OF 'TEST_RANDOM' (Cost=613 Card=2299 Bytes=78166)
  12. Statistics
  13. ----------------------------------------------------------
  14. 0 recursive calls
  15. 0 db block gets
  16. 6415 consistent gets
  17. 4910 physical reads
  18. 0 redo size
  19. 111416 bytes sent via SQL*Net to client
  20. 2182 bytes received via SQL*Net from client
  21. 155 SQL*Net roundtrips to/from client
  22. 0 sorts (memory)
  23. 0 sorts (disk)
  24. 2300 rows processed

因为聚簇因子的缘故,优化器选择了全表扫描而不是使用索引:

BITMAP EMPNO (Range) B-TREE
Consistent Reads Physical Reads Consistent Reads Physical Reads
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树索引,全部使用全表扫描。引起这种差异的原因是聚簇因子:优化器在产生执行计划时不考虑位图索引的聚簇因子,但是对于B树索引来说,则需要 考虑聚簇因子。在上面的情况中,位图索引比B树索引更有效。

下面的步骤揭示了这些索引更有趣的方面。

步骤5.1(在表test_normal上)

在表test_normal的SAL列上建立一个位图索引,该列拥有普通的cardinality。

  1. SQL> create bitmap index normal_sal_bmx on test_normal(sal);
  2. Index created.
  3. SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
  4. Table analyzed.

得到索引的大小和聚簇因子:

  1. SQL>select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2. 2* from user_segments
  3. 3* where segment_name in ('TEST_NORMAL','NORMAL_SAL_BMX');
  4. SEGMENT_NAME Size in MB
  5. ------------------------------ --------------
  6. TEST_NORMAL 50
  7. NORMAL_SAL_BMX 4
  8. SQL> select index_name, clustering_factor from user_indexes;
  9. INDEX_NAME CLUSTERING_FACTOR
  10. ------------------------------ ----------------------------------
  11. NORMAL_SAL_BMX 6001

下面执行查询,首先执行相等性查询:

  1. SQL> set autot trace
  2. SQL> select * from test_normal where sal=&sal;
  3. Enter value for sal: 1869
  4. old 1: select * from test_normal where sal=&sal
  5. new 1: select * from test_normal where sal=1869
  6. 164 rows selected.
  7. Elapsed: 00:00:00.08
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=168 Bytes=4032)
  11. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=39 Card=168 Bytes=4032)
  12. 2 1 BITMAP CONVERSION (TO ROWIDS)
  13. 3 2 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  14. Statistics
  15. ----------------------------------------------------------
  16. 0 recursive calls
  17. 0 db block gets
  18. 165 consistent gets
  19. 0 physical reads
  20. 0 redo size
  21. 8461 bytes sent via SQL*Net to client
  22. 609 bytes received via SQL*Net from client
  23. 12 SQL*Net roundtrips to/from client
  24. 0 sorts (memory)
  25. 0 sorts (disk)
  26. 164 rows processed

接下来是范围查询:

  1. SQL> select * from test_normal where sal between &sal1 and &sal2;
  2. Enter value for sal1: 1500
  3. Enter value for sal2: 2000
  4. old 1: select * from test_normal where sal between &sal1 and &sal2
  5. new 1: select * from test_normal where sal between 1500 and 2000
  6. 83743 rows selected.
  7. Elapsed: 00:00:05.00
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
  11. =2001024)
  12. 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
  13. Bytes=2001024)
  14. Statistics
  15. ----------------------------------------------------------
  16. 0 recursive calls
  17. 0 db block gets
  18. 11778 consistent gets
  19. 5850 physical reads
  20. 0 redo size
  21. 4123553 bytes sent via SQL*Net to client
  22. 61901 bytes received via SQL*Net from client
  23. 5584 SQL*Net roundtrips to/from client
  24. 0 sorts (memory)
  25. 0 sorts (disk)
  26. 83743 rows processed

现在,删除test_normal上的位图索引并且建立一个B树索引。

  1. SQL> create index normal_sal_idx on test_normal(sal);
  2. Index created.
  3. SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
  4. Table analyzed.

查看索引大小和聚簇因子:

  1. SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2. 2 from user_segments
  3. 3 where segment_name in ('TEST_NORMAL','NORMAL_SAL_IDX');
  4. SEGMENT_NAME Size in MB
  5. ------------------------------ ---------------
  6. TEST_NORMAL 50
  7. NORMAL_SAL_IDX 17
  8. SQL> select index_name, clustering_factor from user_indexes;
  9. INDEX_NAME CLUSTERING_FACTOR
  10. ------------------------------ ----------------------------------
  11. NORMAL_SAL_IDX 986778

从上表可以看出,B树索引大于相同列上的位图索引,它的聚簇因子接近于表中的行数。

现在,先执行相等性查询:

  1. SQL> set autot trace
  2. SQL> select * from test_normal where sal=&sal;
  3. Enter value for sal: 1869
  4. old 1: select * from test_normal where sal=&sal
  5. new 1: select * from test_normal where sal=1869
  6. 164 rows selected.
  7. Elapsed: 00:00:00.01
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=169 Card=168 Bytes=4032)
  11. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=169 Card=168 Bytes=4032)
  12. 2 1 INDEX (RANGE SCAN) OF 'NORMAL_SAL_IDX' (NON-UNIQUE) (Cost=3 Card=168)
  13. Statistics
  14. ----------------------------------------------------------
  15. 0 recursive calls
  16. 0 db block gets
  17. 177 consistent gets
  18. 0 physical reads
  19. 0 redo size
  20. 8461 bytes sent via SQL*Net to client
  21. 609 bytes received via SQL*Net from client
  22. 12 SQL*Net roundtrips to/from client
  23. 0 sorts (memory)
  24. 0 sorts (disk)
  25. 164 rows processed

接下来是范围查询:

  1. SQL> select * from test_normal where sal between &sal1 and &sal2;
  2. Enter value for sal1: 1500
  3. Enter value for sal2: 2000
  4. old 1: select * from test_normal where sal between &sal1 and &sal2
  5. new 1: select * from test_normal where sal between 1500 and 2000
  6. 83743 rows selected.
  7. Elapsed: 00:00:04.03
  8. Execution Plan
  9. ----------------------------------------------------------
  10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
  11. =2001024)
  12. 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
  13. Bytes=2001024)
  14. Statistics
  15. ----------------------------------------------------------
  16. 0 recursive calls
  17. 0 db block gets
  18. 11778 consistent gets
  19. 3891 physical reads
  20. 0 redo size
  21. 4123553 bytes sent via SQL*Net to client
  22. 61901 bytes received via SQL*Net from client
  23. 5584 SQL*Net roundtrips to/from client
  24. 0 sorts (memory)
  25. 0 sorts (disk)
  26. 83743 rows processed

在不同的数据集上执行查询的结果如下,可以看出逻辑和物理I/O的次数基本上是相同的。

BITMAP
SAL (Equality)
B-TREE Rows Fetched
Consistent Reads Physical Reads Consistent Reads Physical Reads
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  

BITMAP
SAL (Range)
B-TREE Rows Fetched
Consistent Reads Physical Reads Consistent Reads Physical Reads
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

对于范围查询,优化器选择了全表扫描,根本没有使用索引。但是对于相等性查询,优化器使用了索引。再次,逻辑和物理I/O是相同的。

因此,可以得出结论,对于一个具有normal-cardinality的列来说,优化器对于两种类型的索引的选择是相同的,并且没有明显的I/O差异。

步骤6(增加GENDER列)

在测试low-cardinality列之前,我们先增加一个GENDER列并且把它的值更新成M,F或者null。

  1. SQL> alter table test_normal add GENDER varchar2(1);
  2. Table altered.
  3. SQL> select GENDER, count(*) from test_normal group by GENDER;
  4. S COUNT(*)
  5. - ----------
  6. F 333769
  7. M 499921
  8. 166310
  9. 3 rows selected.

该列上位图索引的大小大约为570KB,如下表所示:

  1. SQL> create bitmap index normal_GENDER_bmx on test_normal(GENDER);
  2. Index created.
  3. Elapsed: 00:00:02.08
  4. SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  5. 2 from user_segments
  6. 3 where segment_name in ('TEST_NORMAL','NORMAL_GENDER_BMX');
  7. SEGMENT_NAME Size in MB
  8. ------------------------------ ---------------
  9. TEST_NORMAL 50
  10. NORMAL_GENDER_BMX .5625
  11. 2 rows selected.

相对而言,改列上的B树索引的大小为13M,比位图索引大的多。

  1. SQL> create index normal_GENDER_idx on test_normal(GENDER);
  2. Index created.
  3. SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  4. 2 from user_segments
  5. 3 where segment_name in ('TEST_NORMAL','NORMAL_GENDER_IDX');
  6. SEGMENT_NAME Size in MB
  7. ------------------------------ ---------------
  8. TEST_NORMAL 50
  9. NORMAL_GENDER_IDX 13
  10. 2 rows selected.

现在,执行相等性查询,优化器将不使用该索引,不论是位图索引还是B树索引,它将使用全部扫描。

  1. SQL> select * from test_normal where GENDER is null;
  2. 166310 rows selected.
  3. Elapsed: 00:00:06.08
  4. Execution Plan
  5. ----------------------------------------------------------
  6. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=166310 Bytes=4157750)
  7. 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=166310 Bytes=4157750)
  8. SQL> select * from test_normal where GENDER='M';
  9. 499921 rows selected.
  10. Elapsed: 00:00:16.07
  11. Execution Plan
  12. ----------------------------------------------------------
  13. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=499921 Bytes=12498025)
  14. 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=499921Bytes=12498025)
  15. SQL>select * from test_normal where GENDER='F'
  16. /
  17. 333769 rows selected.
  18. Elapsed: 00:00:12.02
  19. Execution Plan
  20. ----------------------------------------------------------
  21. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=333769 Byte
  22. s=8344225)
  23. 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=333769
  24. Bytes=8344225)

结论

现在我们了解了优化器对于这些技术做出的反应,现在我们来看对于位图索引和B树索引最适合的程序。

保持GENDER列上的位图索引,在SAL列上再建立一个位图索引然后执行一些查询。对这些列上的B树索引执行同样的查询。

在表test_normal中,你需要所有工资等于下列值的所有女性雇员的雇员号码:

1000
1500
2000
2500
3000
3500
4000
4500

因此:

  1. SQL>select * from test_normal
  2. where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

这是一个典型的数据仓库查询,绝对不要在OLTP系统中执行该查询。下面是两列上具有位图索引时的结果:

  1. SQL>select * from test_normal
  2. where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';
  3. 1453 rows selected.
  4. Elapsed: 00:00:02.03
  5. Execution Plan
  6. ----------------------------------------------------------
  7. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=198 Card=754 Bytes=18850)
  8. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=198 Card=754 Bytes=18850)
  9. 2 1 BITMAP CONVERSION (TO ROWIDS)
  10. 3 2 BITMAP AND
  11. 4 3 BITMAP OR
  12. 5 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  13. 6 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  14. 7 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  15. 8 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  16. 9 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  17. 10 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  18. 11 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  19. 12 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  20. 13 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  21. 14 3 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_GENDER_BMX'
  22. Statistics
  23. ----------------------------------------------------------
  24. 0 recursive calls
  25. 0 db block gets
  26. 1353 consistent gets
  27. 920 physical reads
  28. 0 redo size
  29. 75604 bytes sent via SQL*Net to client
  30. 1555 bytes received via SQL*Net from client
  31. 98 SQL*Net roundtrips to/from client
  32. 0 sorts (memory)
  33. 0 sorts (disk)
  34. 1453 rows processed

下面是B树索引时的结果:

  1. SQL>select * from test_normal
  2. where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';
  3. 1453 rows selected.
  4. Elapsed: 00:00:03.01
  5. Execution Plan
  6. ----------------------------------------------------------
  7. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=754 Bytes=18850)
  8. 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=754 Bytes=18850)
  9. Statistics
  10. ----------------------------------------------------------
  11. 0 recursive calls
  12. 0 db block gets
  13. 6333 consistent gets
  14. 4412 physical reads
  15. 0 redo size
  16. 75604 bytes sent via SQL*Net to client
  17. 1555 bytes received via SQL*Net from client
  18. 98 SQL*Net roundtrips to/from client
  19. 0 sorts (memory)
  20. 0 sorts (disk)
  21. 1453 rows processed

可以看出,如果使用B树索引,优化器使用全表扫描;而对于位图索引,则使用索引。从获取结果需要的I/O次数可以推断出性能。

总之,基于如下的原因,位图索引适用于决策支持系统,而不管cardinality的高低:

  • 使用位图索引,优化器可以高效地执行包含AND,OR或者XOR的查询。
  • 使用位图索引,优化器可以回答对null的查询和计数。null值在位图索引时同样被加上索引(不像B树索引)。
  • 最重要的是,在决策支持系统中,位图索引支持特殊的查询,但B树索引则不能。具体来说,如果你有一个包含50列的表,用户经常查询其中的10列 ---- 10列的组合或者有时是其中一列,创建B树索引会比价困难。如果你在这些列上建立10个位图索引,这些查询都可以通过索引回答,不论你查询的是全部10列,还是10列中的4列或者6列,或者其中的一列。

相反,B树索引非常适合于OLTP系统,其用户执行的都是常规的查询。因为在OLTP系统中,数据会频繁地更新和删除,如果使用位图索引将会引起严重的锁定性能问题。

两种索引都有一个共同的目的:尽快地得到结果。但是你应该依据程序的类型来选择其中之一,而不是根据cardinality水平。

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Oracle B-tree、位图、全文索引三大索引性能比较及优缺点汇总

引言:大家都知道“效率”是数据库中非常重要的一个指标,如何提高效率大家可能都会想起索引,但索引又这么多种,什么场合应该使用什么索引呢?哪种索引可以提高我们的效率,哪种索引可以让我们的效率大大降低(有时...

B树索引和位图索引的区别!

B树索引 主键和唯一性约束字段的B树索引,效率几乎和海量数据没有关系。 键值重复率低的字段比较适合使用B树索引。 位图索引 键值重复率高的字段比较适合使用位图索引。 count、an...

Oracle - 怎样使用B树索引和位图索引

http://blog.csdn.net/tswisdom/article/details/7396826   注:low-cardinality是指该列或者列的组合具有的不同值的个数较少,即该列...

oracle 位图索引 B-树索引

B-树索引在Oracle中是一个通用的索引,在创建索引时它就是默认的索引类型。最多可以包括32列。 位图索引Oracle为每个唯一键创建一个位图,然后把与键值所关联的ROWID保存为位图。最多可...

oracle四招提高位图索引的使用效果

位图索引是Oralce数据库索引中的异类,其在某些比较特殊的场合中有突出的表现。一般来说,位图索引的效果直接跟列的基数相关。为此在谈到如何提高位图索引的使用效果时,也往往跟这个列的基数相关。为此必须对...

Oracle中B-Tree索引与Bitmap位图索引的锁代价比较研究

转载自:http://blog.itpub.net/519536/viewspace-611296/通过以下实验,来验证Bitmap位图索引较之普通的B-Tree索引锁的“高昂代价”。位图索引会带来“...

Oracle编程高手箴言:位图索引(Bitmap Index)的故事

您如果熟悉Oracle数据库,我想您对Thomas Kyte的大名一定不会陌生。Tomas主持的asktom.oracle.com网站享誉Oracle界数十年,绝非幸致。最近在图书馆借到这位Oracl...

Oracle位图索引引发的阻塞与死锁

前面我介绍了itl引发的阻塞与死锁,这里有必要再介绍一下位图索引引发的阻塞与死锁,因为这个也是不同于普通死锁的一种死锁方式,在有位图索引存在的表上面,其实很容易就引发阻塞与死锁。这个阻塞不是发生在表上...

Oracle编程艺术学习笔记-位图索引(bitmap index)

对于B*树索引,通常索引条目和行之间存在一种一对一的关系:一个索引条目就指向一行。 而对于位图索引,一个索引条目则使用一个位图同时指向多行。 使用create bitmap index...来创建...

详解oracle bitmap位图索引

位图索引是oracle中非常重要的一种索引形式。本文通过总结有关位图索引的资料,尝试回答如下几个问题: 1:什么是位图索引? 2:位图索引适合什么场景,不适合什么场景? 3:位图索引的性能如何?...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)