2.1.2 全表扫描

说到索引扫描,不得不提的就是全表扫描(FULL TABLE SCAN)了,因为在一定程度上,引进索引扫描就是为了取代全表扫描。

全表扫描(FULL TABLE SCAN)就是在数据查询过程中,对整张表的全部低于高水位标记(High Water Mark,HWM)的数据块(Data Block)进行读取。如图2-2所示,可以说单次查询需要读取全表的数据,对于小表来说,这是无可厚非的,甚至可能是最优的方式。但如果是一张数据量较大的表,这将导致很多非必要的数据块读取,造成过多的IO开销。

 book_ch02_01

图2-2  全表扫描

从另一方面来讲,判断一次索引扫描是否高效的标准就是将其与全表扫描进行比较,如果较之成本更低,那么索引扫描可以被视为高效的,反之则是需要优化的。

通过一个例子来简单对比一下吧。从返回结果来看,表alex_t00有10万行记录,不算一个小表了,执行计划的成本开销(COST)中,全表扫描COST=84,而索引扫描COST=44,全表扫描的执行效率将是非常低下的。两种扫描的效率对比如下所示:

SQL> select /*+full(alex_t00)*/ count(*) from alex_t00;
  COUNT(*)
----------
    100000
-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    84   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ALEX_T00 |   100K|    84   (2)| 00:00:02 |
-----------------------------------------------------------------------

SQL> select count(*) from alex_t00;
  COUNT(*)
----------
    100000
-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    44   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_ALEX_T00 |   100K|    44   (3)| 00:00:01 |
-----------------------------------------------------------------------------

提示

全表扫描,对于小表来说是最优选择,对于没有合适的索引的大表上来说,也是不错的选择。

2.1.3 ROWID扫描

我们已经了解到ROWID其实就是索引的“页码”,它是Oracle提供的伪列,一般说来每一行数据都对应一个固定且唯一的ROWID,在这一行数据存入数据库的时候就确定了。ROWID扫描查询示例如下所示:

SQL> select rowid from alex_t00 where id=1;
ROWID
------------------
AAA3YkAAEAAAAvlAAA
SQL> explain plan for select * from alex_t00
2  where rowid='AAA3YkAAEAAAAvlAAA';
------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost    |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |    32 |     1   |
|   1 |  TABLE ACCESS BY USER ROWID| ALEX_T00 |     1 |    32 |     1   |
------------------------------------------------------------------------

从上面查询例子可以看到,ROWID是基于64位编码的18个字符显示,它记录了数据对象的编号、文件编号、块编号、行编号,即数据行存储的物理位置,如表2-1所示:

表2-1  ROWID组成

数据对象编号

文件编号

块编号

行编号

AAA3Yk

AAE

AAAAvl

AAA

利用ROWID来查询记录,其实就是根据数据行实际存储的位置来获取数据。通过ROWID查询记录是查询速度最快的查询方法,比任何索引扫描方式都要快速。为什么这么说呢?我们说索引扫描实质上可以分解成两个动作:

q  索引结构扫描,获取待返回数据行的ROWID;

q  根据获取的ROWID扫描表,获取对应数据行,并返回。

ROWID的扫描方式其实就是索引扫描的第二个动作,换而言之,索引扫描的目标就是通过ROWID扫描的方式从表中获取查询数据行。

通过dbms_rowid这个包,可以直接得到具体的ROWID所包含的信息:

SQL> select dbms_rowid.rowid_object(rowid) object_id,
  2         dbms_rowid.rowid_relative_fno(rowid) file_id,
  3         dbms_rowid.rowid_block_number(rowid) block_id,
  4         dbms_rowid.rowid_row_number(rowid) num
  5    from alex_t00
  6   where id = 1;
 OBJECT_ID     FILE_ID    BLOCK_ID         NUM
---------- ---------- ---------- ----------
     226852            4        3045            0

提示

ROWID扫描方式是查询取数最快的方式,索引检索的本质也是转换为ROWID扫描取数。

2.1.4 索引唯一扫描

从上面的介绍,我们可以了解到索引扫描的过程其实是扫描索引结构获取ROWID的过程。索引唯一扫描(INDEX UNIQUE SCAN)只能发生在唯一键索引(主键索引实质即为唯一键索引)上,通过唯一索引查找数值往往返回单个ROWID,如图2-3所示,从索引的根(root)节点到枝(branch)节点,再到叶(leaf)节点上存储着一个对应的ROWID,即对应的查询结果也只返回一行,这种存取方法称为“索引唯一扫描”。如果该唯一索引是由多个列组成的组合索引,则至少要有组合索引的前导列参与到该查询中,同样SQL语句只返回一行记录,这也属于索引唯一扫描。

 book_ch02_02

图2-3  索引唯一扫描

下面通过一些实例来了解一下,该扫描方式的特点。在正式开始之前,我们需要做一点准备工作:

步骤1  创建一下相关的表和主键索引:

SQL> create table alex_t01 (
  2    id number,
  3    a number,
  4    b number,
  5    c number,
  6    name varchar2(100)
7    );

SQL> alter table alex_t01 add constraint pk_alex_t01
2   primary key (id) using index;

步骤2  初始化数据,顺序地插入10万行数据:

SQL> declare
  2  begin
  3  for i in 1 .. 100000 loop
  4    insert into alex_t01
  5    values
  6      (i, mod(i, 2), mod(i, 20000), mod(i, 20000), 'alex');
  7  end loop;
  8  commit;
  9  end;
 10  /

步骤3  最重要的是收集一下表和主键索引的统计信息和直方图信息(默认开启直方图收集),在缺失统计信息和直方图的情况下,CBO优化器可能无法正确地计算SQL语句的执行成本,直接导致执行计划跑偏,影响性能。

SQL> exec dbms_stats.gather_table_stats('alex','alex_t01')

SQL> exec dbms_stats.gather_index_stats('alex','pk_alex_t01')

准备工作完成后,可以实际执行一下查询SQL语句,进行如下所示的验证。

SQL> select id, name from alex_t01 where id=400;
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01    |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ALEX_T01 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

我们看到查询筛选条件为id=400,是一个等值查询,返回唯一数据行,执行计划走的是索引唯一扫描方式。

如果这里不是等值查询呢?那么,执行计划将无法按索引唯一扫描方式。换而言之,有且仅当唯一键索引列上发生等值查询时,才会触发索引唯一扫描,返回单行数据。这种索引扫描方式也是最高效的索引扫描方式,常见于主键索引的应用场景。

我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引扫描,如下所示:

SQL> select /*+ index(alex_t01 pk_alex_t01) */ id, name
2   from alex_t01 where id=400;

提示

索引唯一扫描是最高效的索引扫描方式,其只对唯一键索引上的等值查询有效。

2.1.5 索引范围扫描

索引的使用过程中,更多的情况是返回多个数据行,当使用一个索引存取多行数据时,这种索引扫描方式称为“索引范围扫描”(INDEX RANGE SCAN)。与索引唯一扫描不同,索引范围扫描可以发生在唯一键索引上,也可以发生在非唯一索引上。

哪些情况会发生索引范围扫描呢?

q  在唯一索引列上使用了范围操作符(如:>、<、<>、>=、<=、between,即不等值查询)。

q  对非唯一索引列上进行的查询。

 book_ch02_03

图2-3  索引范围扫描

先来看看第一种情况,在主键索引列上进行非等值查询,筛选条件为id<4,返回了3行数据,此时的执行计划走的不是索引唯一扫描了,而是索引范围扫描,如下例所示:

SQL> select id, name from alex_t01 where id<4;
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_ALEX_T01 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

再来测试一下第二种情况,为表alex_t01追加一个单列索引和一个组合索引,并收集相关统计信息和直方图。

SQL> create index idx_alex_t01_id_ab on alex_t01 (a, b);
SQL> create index idx_alex_t01_id_c on alex_t01 (c);
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t01_id_ab')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t01_id_c')

在非唯一键索引idx_alex_t01_id_c的索引列c上进行查询,其执行计划走的是索引范围扫描。而在普通索引上的查询,不论是否等值查询,也不论返回的数据行数是多少,其执行计划均为索引范围扫描。索引范围扫描示例如下所示:

SQL> select * from alex_t01 where c=100;
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     5 |   105 |     6| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01          |     5 |   105 |     6| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T01_ID_C |     5 |       |     1| 00:00:01 |
-------------------------------------------------------------------------------------------

我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引范围扫描,如下所示:

SQL> select /*+ index_rs(alex_t01 pk_alex_t01) */ id, name
2   from alex_t01 where id<4;

当发生索引范围扫描的时候,对索引列有一个自动排序操作,默认情况下是正序(ASC)输出返回的结果集的,也就是INDEX RANGE SCAN ASC。对于本例来说,以下两句SQL语句是等效的:

SQL> select * from alex_t01 where c=100;
SQL> select * from alex_t01 where c=100 order by c;

如果在SQL语句中要求反序排序输出结果集呢?索引排序具体内容将在接下来的章节展开。

提示

索引范围扫描是最常见的一种索引扫描方式,在做优化时,需要尽可能的使用的一种方式。

2.1.6 索引全扫描

对于表来说,有全表扫描,同样对于索引来说,也是存在索引全扫描的。索引全扫描(INDEX FULL SCAN)与全表扫描是非常类似的,如图2-4所示,它将先扫描索引全部节点和条目,再选择对应数据进行排序输出。索引全扫描只在CBO模式下才有效。 CBO根据统计数值得知进行索引全扫描比进行全表扫描更有效时,才进行索引全扫描,而且此时查询出的数据都必须从索引中可以直接得到。

 book_ch02_04

图2-4  索引全扫描

一般来说哪些情况会使用到索引全扫描呢?

q  表和表进行排序合并联立(Sort-Merge Join)查询的时候,排序的列必须是存在于索引中的;

q  查询中有order by和group by子句的时候,子句中所有的列也是必须存在于索引中的。

如下是一个简单索引全扫描的例子:

SQL> select * from alex_t01 order by id;
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   100K|  2050K|   560   (2)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01    |   100K|  2050K|   560   (2)| 00:00:07 |
|   2 |   INDEX FULL SCAN           | PK_ALEX_T01 |   100K|       |   191   (2)| 00:00:03 |
-------------------------------------------------------------------------------------------

我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引全扫描,如下所示:

SQL> select /*+ index_fs(alex_t01 pk_alex_t01) */ *
2   from alex_t01 order by id;

与全表扫描相比,索引全扫描的优势在哪里呢?

q  全表扫描过程是不进行排序的,必须将数据全部取出后,再进行排序输出,其扫描目标表HWM下所有数据块,包括没有必要的空块。

q  因为索引结构本身就是一个有序的结构,索引全扫描在遍历索引的同时就已经完成了排序操作,在输出结果的时候是不需要再排序的,再者其通过ROWID获取行数据,避免了空块的读取。

提示

索引全扫描过程是单块读取,其不支持多块并行的读取,输出结果是有序排列的。

2.1.7 索引快速全扫描

索引快速全扫描(INDEX FAST FULL SCAN)是扫描索引中的所有数据块,与 INDEX FULL SCAN很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。这种存取方法中,可以使用多块读功能,也可以使用并行读,以便获得最大吞吐量并缩短执行时间。

看一看下面的例子,复合索引idx_alex_t01_id_ab的索引列为(a, b),查询的返回列a,b都包含在索引列上,这个时候的取数操作直接就能在索引上完成了,不需要再根据ROWID去表中取数了,而且没有排序的需求。这时执行计划走的就是INDEX FAST FULL SCAN的操作了。

SQL> select a, b from alex_t01 where b>600;
-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    | 97038 |   758K|    83   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_ALEX_T01_ID_AB | 97038 |   758K|    83   (4)| 00:00:01 |
-------------------------------------------------------------------------------------------

当我们取count(*)的时候,同样是不关心顺序的,也不需要排序操作,该查询只需要统计索引叶节点上的索引条目数量就可返回结果了,INDEX FAST FULL SCAN是一个非常好的选择。在下面的执行计划中示例中,我们可以看到,SORT AGGREGATE操作是没有意义的,因为排序行数只有1行。

SQL> select count(*) from alex_t01;
-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     5 |    44   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_ALEX_T01 |   100K|   488K|    44   (3)| 00:00:01 |
-------------------------------------------------------------------------------------

我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引快速全扫描,如下所示:

SQL> select /*+ index_ffs(alex_t01 idx_alex_t01_id_ab) */ a, b
2   from alex_t01 where b>600;

再来对比一下索引全扫描和索引快速全扫描,如表2-2所示:

表2-2  索引全扫描与快速全扫描对比

扫描方式

输出排序

取数效率

索引全扫描

有序输出,无需额外排序

单块读,不支持并行

索引快速全扫描

无序输出,需要额外排序

支持并行多块读

提示

索引快速全扫描是一种比较高效的扫描方式,在优化过程中,可以尽量多使用。

2.1.8 索引跳跃扫描

索引跳跃扫描(INDEX SKIP SCAN)是Oracle 9i引进的一个新特性,其发生在复合索引上,如果SQL语句中WHERE子句只包含索引中的部分列,且这些列不是索引的第一列,就可能发生INDEX SKIP SCAN。如果在查询时,第一列没有被指定,就跳过它。

INDEX SKIP SCAN除了需要CBO,并且对表进行过分析外,还需要保证第一列的distinct值非常小。Oracle会对复合索引进行逻辑划分,分为多个子索引,可以理解为索引从逻辑上被划分为第一列distinct值的数量的子索引,每次对一个子索引进行扫描。

下面通过一个例子来分析一下,在表alex_t01上,有一个复合索引idx_alex_t01_id_ab,索引列为(a, b),查询一下该表A列的distinct值的数量为2,即只有“0”和“1”两个键值,是满足了先决条件的。

SQL> select distinct a from alex_t01;
          A
----------
           1
           0

再进行一次INDEX SKIP SCAN类型的查询,示例如下所示:

SQL> select a, b, name from alex_t01 where b=600;
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     5 |    65 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01           |     5 |    65 |     8   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_ALEX_T01_ID_AB |     5 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

可以看到COST开销是非常小的。如图2-5所示,此时,我们可以理解成复合索引idx_alex_t01_id_ab(a,b)逻辑上被拆分成两个独立子索引idx_alex_t01_id_ab_<a=0>(b)和idx_alex_t01_id_ab_<a=1>(b),where子句中b=600的查询将分别对这两个子索引进行扫描。

 book_ch02_05

图2-5  索引跳跃扫描

这时,如果a列的distinct值很多,那么复合索引idx_alex_t01_id_ab拆分逻辑子索引的动作本身就不小的开销,查询过程再逐个扫描子索引也会增加开销,相比之下,CBO优化器可能会更倾向于选择全表扫描。

我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引跳跃扫描,如下所示:

SQL> select /*+ index_ss(alex_t01 idx_alex_t01_id_ab) */ a, b, name

2  from alex_t01 where b=600;

但是,换一个角度来思考,我们会在设计索引的时候设计一个前导列区分度极低的复合索引吗?一般情况下,我们是不会这么做的。这又意味着什么呢?这意味着在执行计划中,如果看到INDEX SKIP SCAN,其COST开销将会非常大的,反而成了我们需要优化的对象。

提示

在复合索引设计中,尽可能选择区分度较大的列作为前导列。如果为了使用INDEX SKIP SCAN这个索引扫描方式而选择区分度极低的列作为前导列,就是本末倒置了。

2.1.9 索引组合扫描

如果一个查询语句中,WHERE子句包含两个筛选条件,这两个条件都有其单独的索引,我们是不是可以同时使用两个索引呢?答案是肯定的。我们可以通过两个独立的索引分别扫描,再组合起来。在Oracle早期的版本中,我们可以通过and_equal方式来实现。从Oracle 10g开始,and_equal方式已经被废弃,由index_combine方式取而代之。

索引组合(INDEX COMBINE)最早是出现在位图索引上的,从Oracle 9i开始,默认可以使用在B树索引上,这个特性是由隐藏参数_b_tree_bitmap_plans来控制的。Oracle将B树索引中获得的ROWID信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成位图进行匹配,完成后通过BITMAP CONVERSION TO ROWIDS再转换出ROWID获得数据或者回表获得数据。

通过一个例子来看一下吧。在开始之前,我们需要修改一下表alex_t01上的索引,我们需要删除掉组合索引idx_alex_t01_id_ab,为b列创建一个单列索引idx_alex_t01_id_b,并重新收集统计信息。SQL语句如下:

SQL> drop index idx_alex_t01_id_ab;
SQL> create index idx_alex_t01_id_b on alex_t01 (b);
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t01_id_b')

此时,b列和c列都有了其独立的单列索引,且此两列区分度都较高。我们再来做一次基于b列和c列的组合查询试试。

SQL> select * from alex_t01 where b=600 and c=600;
-------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     1 |    21 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | ALEX_T01          |     1 |    21 |     2   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS    |                   |       |       |            |
|   3 |    BITMAP AND                    |                   |       |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |
|*  5 |      INDEX RANGE SCAN            | IDX_ALEX_T01_ID_B |     5 |       |     1   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |
|*  7 |      INDEX RANGE SCAN            | IDX_ALEX_T01_ID_C |     5 |       |     1   (0)|
-------------------------------------------------------------------------------------------

如果你因为看到BITMAP CONVERSION的字样而感到担忧的话,那大可不必了,这部分的COST基本可以忽略,这是一个典型的index_combine例子。

我们要是强制查询只走其中一个索引呢,情况会如何呢?看一个示例:

SQL> select /*+index(alex_t01,idx_alex_t01_id_b)*/ *
2  from alex_t01 where b=600 and c=600;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    21 |     6   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01          |     1 |    21 |     6   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T01_ID_B |     5 |       |     1   (0)|
--------------------------------------------------------------------------------------
SQL> select /*+index(alex_t01,idx_alex_t01_id_c)*/ *
2  from alex_t01 where b=600 and c=600;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    21 |     6   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01          |     1 |    21 |     6   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T01_ID_C |     5 |       |     1   (0)|
--------------------------------------------------------------------------------------

从上例可以看到,不论是走b列的索引还是走c列的索引,其COST开销都不如index_combine方式更优。

换而言之,如果我们知道索引组合扫描的方式会更优,也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引组合扫描,示例如下所示:

SQL> select /*+ index_combine(alex_t01 idx_alex_t01_id_b
2   idx_alex_t01_id_c) */ * from alex_t01 where b=600 and c=600;

2.1.10 索引联立扫描

2.1.9节说到,若一个查询语句中,WHERE子句包含两个都有单独的索引筛选条件,则我们可以用index_combine扫描的方式来进行优化,但是index_combine仍然是需要有回表取数的操作。如果我们查询返回的列都包含在该两个索引中,我们就可以不用回表取数了,直接通过两个索引的HASH JOIN来完成了。这个时候需要用过另一个索引相关的HINT关键字index_join。

通过下面的例子来看一下,CBO优化器更倾向于COST更低的index_combine扫描,强制执行计划走index_join扫描,COST较index_combine扫描要高一些,但是相对单一索引的使用来说,却是有优势的。

SQL> select /*+ index_join(alex_t01 idx_alex_t01_id_b
2  idx_alex_t01_id_c) */ b, c from alex_t01 where b=600 and c=600;
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |    10 |     3  (34)| 00:00:01 |
|*  1 |  VIEW              | index$_join$_001  |     1 |    10 |     3  (34)| 00:00:01 |
|*  2 |   HASH JOIN        |                   |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_ALEX_T01_ID_B |     1 |    10 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| IDX_ALEX_T01_ID_C |     1 |    10 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

严格意义来说,index_combine和index_join都不能算是一种独立的索引扫描方式,它们是对现有五种索引扫描方式的优化和补充,使其获得更好的性能优势。

提示

INDEX COMBINE和INDEX JOIN扫描方式各自有其适用场景,合理的使用索引组合和索引联立会带来性能的大幅提升。

如果索引结构设计比较合理,则能在索引扫描过程中完成取数的操作,尽量在索引扫描中完成,避免回表取数的开销,这个技巧叫做索引覆盖应用(INDEX COVERING),它覆盖了查询的所有字段(select、 where、 order by、group by),用来提高查询的效率。

纵观各种索引扫描方式的介绍和分析,每种扫描方式都有其特点和适用场景,不能单纯地说哪种扫描方式更优。在优化的工作中,更不能简单去用某种扫描方式去替代另一种扫描方式,我们需要分析清楚具体的应用场景,根据业务需求选择合适的索引扫描方式。

如果统计信息和直方图收集得准确的话,CBO优化器会提供准确的COST开销估算,可以作为索引扫描方式选择的很好参考。在实际优化的工作中,往往我们不能获得足够准确的统计信息和直方图信息,就需要比较不同索引扫描方式下,SQL语句执行的响应时间来判断。

Trackback

no comment untill now

Add your comment now

切换到手机版