Oracle中的B树索引

Posted on by YongJing(敬勇)

B树索引是Oracle的默认的索引类型。因为表中的行标识符(rowid)和相关的列值存储在一个平衡的树状结构的索引块中，所以该索引类型被称为B树索引。使用Oracle的B树索引有以下几个原因.
.提高SQL语句的性能
.强制执行主键和唯一键约束的唯一性
.减少通过主键和外键约束关联的父表和子表间潜在的锁定问题

Oracle如何使用B树索引
为了充分理解B树索引的内部实现，以便在建立数据库应用程序时能做出明智的索引决定。将举例说明，首先创建测试表cust

SQL> create table cust(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30));

Table created.

在last_name列上创建B树索引

SQL> create index cust_idx1 on cust(last_name);

Index created.

向表cust中插入数据

SQL> insert into cust(cust_id,last_name,first_name)
  2  select rownum rn ,a.last_name,a.first_name from hr.employees a
  3  union
  4  select rownum+(107*1) rn ,a.last_name,a.first_name from hr.employees a
  5  union
  6  select rownum+(107*2) rn ,a.last_name,a.first_name from hr.employees a
  7  union
  8  select rownum+(107*4) rn ,a.last_name,a.first_name from hr.employees a
  9  union
 10  select rownum+(107*5) rn ,a.last_name,a.first_name from hr.employees a
 11  union
 12  select rownum+(107*6) rn ,a.last_name,a.first_name from hr.employees a
 13  union
 14  select rownum+(107*7) rn ,a.last_name,a.first_name from hr.employees a
 15  union
 16  select rownum+(107*8) rn ,a.last_name,a.first_name from hr.employees a
 17  union
 18  select rownum+(107*9) rn ,a.last_name,a.first_name from hr.employees a
 19  union
 20  select rownum+(107*10) rn ,a.last_name,a.first_name from hr.employees a;

1070 rows created.

SQL> commit;

Commit complete.

SQL> select distinct last_name,first_name from cust where rownum<11;

LAST_NAME                      FIRST_NAME
------------------------------ ------------------------------
Austin                         David
Banda                          Amit
Atkinson                       Mozhe
Bissot                         Laura
Ande                           Sundar
Bates                          Elizabeth
Bell                           Sarah
Bernstein                      David
Baer                           Hermann
Baida                          Shelli

10 rows selected.

插入数据后，确保该表的统计信息是最新的，以便为查询优化器提供足够的信息，从而做出如何检索数据的更好决定，执行如下命令收集表的统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'CUST',cascade=>true);

PL/SQL procedure successfully completed.

不建议使用analyze语句(带compute和estimate子句)来收集统计信息。提供此功能只是为了向后兼容。

当向表中插入数据时，Oracle将分配由物理数据库块组成的区。Oracle还将为索引分配数据块。对于每个插入到表中的记录，Oracle还将创建一个包含rowid和列值的索引条目(本例中是rowid和last_name列的值)。每个索引项的rowid指向存储该表的列值的数据文件和数据块号。

当从一个表及其对应的索引选择数据时，存在三种情况。
.SQL查询所需的所有表的数据都在索引结构中。因此，只需要访问索引块。不需要从表中读取数据块。
.查询所需的所有信息没有都包含在索引块中。因此，查询优化器选择既访问索引块也要访问表块来检索需要的数据，以满足查询条件。
.查询优化器选择不访问索引。因此只访问表块。

场景1.所有的数据位于索引块中
这里将介绍两种情况。在每种情况下，执行查询需要的所有数据，包括返回给用户的数据，以及在where子句中被评估的数据，都位于该索引中。
.索引范围扫描(index range scan):如果优化器确定它使用索引结构检索查询所需的多个行时是有效的，那么就使用这种扫描。索引范围扫描被广泛用于各种各样的情况。

.索引快速全扫描(index fast full scan):如果优化器确定表中的大部分行需要进行检索，那么就使用这种扫描。但所有需要的信息都存储在索引中。由于索引结构通常比表结构小，优化器确定全索引扫描(比全表扫描)更高效。这种情况对统计(count)值的查询是很常见的。

首先演示的是索引范围扫描。在这种情况下，运行下面的查询:

select last_name from cust where last_name='Austin';

为了在该查询中返回数据，Oracle最小需要读取多少块，也就是说为了满足此查询，访问物理块最有效的方式是什么，优化器可以选择读取表结构的每个块。然而，这会导致很大的IO开销，因此，它不是检索数据的最优化方法。

对于这个例子，检索数据最有效的方法是使用索引结构。要返回包含last_name列中值为Austin的行，Oracle将需要读取3个索引块。通过使用Oracle的autotrace(自动跟踪)实用程序，可以确认。

SQL> set autotrace on
SQL> select last_name from cust where last_name='Austin';

LAST_NAME
------------------------------
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3472749082

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |    10 |    80 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| CUST_IDX1 |    10 |    80 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("LAST_NAME"='Austin')

此输出显示，Oracle只需要使用cust_idx1索引来检索数据，以满足查询的结果集。不需要访问表中的数据块，只需要访问索引块。这对于给定的查询，这是特别高效的索引策略。当索引包含查询所需的所有列值时，它被称为覆盖索引。

下面列出为这个例子使用自动跟踪所显示的统计信息:

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        653  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

一致获取(consistent gets)的值表示有三个读内存操作。数据库块获取(db block gets)加一致获取等于总的内存读取操作。由于索引块已经在内存中，因此返回此查询的结果集不需要物理读取。此外，有10行进行了处理，这与cust表中last_name为Austin的记录数相符。

下面显示导致执行索引快速全扫描的一个例子。

select count(last_name) from cust;

使用set autotrace on生成执行计划。下面是相应的输出:

SQL> select count(last_name) from cust;

COUNT(LAST_NAME)
----------------
            1070


Execution Plan
----------------------------------------------------------
Plan hash value: 2246355899

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     8 |            |          |
|   2 |   INDEX FAST FULL SCAN| CUST_IDX1 |  1070 |  8560 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

此输出显示，确定表内的计数只用到了索引结构。在这种情况下，优化器确定采取索引快速全扫描比全表扫描更高效。

Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
        559  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

场景2.索引中不包含所有信息
考虑这样一种情况:假设需要从cust表获得更多信息。首先，回顾一下前面的查询语句，并且还要在查询结果中返回first_name列。现在，要获得新增的数据元素，就需要访问表本身。下面是新的查询语句:

select last_name,first_name from cust where last_name='Austin';

使用set autotrace on,并执行前面的查询语句:

SQL> alter system flush buffer_cache;

System altered.

SQL> select last_name,first_name from cust where last_name='Austin';

LAST_NAME                      FIRST_NAME
------------------------------ ------------------------------
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2100940648

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |    10 |   150 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUST      |    10 |   150 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CUST_IDX1 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAST_NAME"='Austin')

此输出信息指示，cust_idx1索引是通过一次索引范围扫描(index range scan)访问的。索引范围扫描标识出满足此查询结果所需的索引块。此外，表是过table access by index rowid batched来读取的。通过索引的rowid访问表，表示Oracle利用存储在索引中的rowid找到表块包含的相应行。把rowid映射到相应的表块，这些块中含有last_name值为Austin的数据。由于我们清空了buffer cache了，这样查询共执行了6次物理读取，9次内存读取。

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          6  physical reads
          0  redo size
        896  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

场景3.只有表块被访问
在某些情况下，即使有索引存在，Oracle也会确定只使用表块比通过索引访问更为有效。当Oracle检查表内的每一行时，这被称为全表扫描。
例如，执行此查询:

SQL> select * from cust;

下面是相应的执行计划和统计信息:

Execution Plan
----------------------------------------------------------
Plan hash value: 260468903

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1070 | 19260 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CUST |  1070 | 19260 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        204  recursive calls
          9  db block gets
        389  consistent gets
         20  physical reads
       1080  redo size
      38869  bytes sent via SQL*Net to client
       1405  bytes received via SQL*Net from client
         73  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
       1070  rows processed

此输出显示，需要一致读取(consistent gets)389个块以及数据库块获取(db block gets)9个块和物理读取20个块。Oracle检索表中的每一行以返回满足查询所需的结果。在这种情况下，必须读取表中已使用的所有块，Oracle无法使用索引来加快数据检索。

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.