2.2.2 输出排序

在使用索引优化查询的过程中,一方面是通过索引扫描快速定位返回行的ROWID,另一方面则是利用索引的有序结构避免一些不必要的排序操作。

下面我们将结合2.1节中介绍的五种索引扫描方式来讨论一下使用索引扫描输出结果集的排序问题吧。

q  索引唯一扫描:这种扫描方式,输出的结果集只有一个记录行,就不存在排序的问题;

q  索引范围扫描:该方式是索引排序最典型的应用,其输出结果集都是有序的(升序输出还是降序输出取决于索引创建时的参数,默认为升序);

q  索引全扫描:这个方式可以看作是全范围的扫描,其效果和索引范围扫描是一样的;

q  索引快速全扫描:从字面上来看,和索引全扫描非常相像,但两者是完全不同的两种方式,快速全扫描的输出结果集是不排序的,如果WHERE子句中要求ORDER BY,则需要额外的排序开销;

q  索引跳跃扫描:可以视作是分拆成多个逻辑子索引后的INDEX COMBINE扫描,对于逻辑子索引的扫描即为索引范围扫描或者索引全扫描。

下面我们通过几个实例对比来具体分析一下几个比较典型的扫描方式的排序情况:索引范围扫描、索引快速全扫描、索引跳跃扫描。

1.  索引范围扫描排序

首先,要创建一个测试表,2.1节的alex_t01表都是顺序插入的连续性的数据,这对于排序的识别有些困难,我们重新创建一个表alex_t02,修改b列和c列的数据为随机数插入。具体步骤和SQL语句如下所示:

步骤1  创建表ALEX_T02,及主键索引和普通索引:

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

SQL> alter table alex_t02 add constraint pk_alex_t02
2   primary key (id) using index;
SQL> create index idx_alex_t02_ab on alex_t02 (a, b);
SQL> create index idx_alex_t02_c on alex_t02 (c);

步骤2  初始化数据,随机的插入10万行数据:

SQL> declare
  2  begin
  3  for i in 1 .. 100000 loop
  4    insert into alex_t02
  5    values
6      (i,
7       mod(i, 2),
8       trunc(dbms_random.value(1, 20000)),
9       trunc(dbms_random.value(1, 20000)),
10       dbms_random.string('X', 10));
11  end loop;
12  commit;
13  end;
14  /

步骤3  收集表和索引的统计信息及直方图信息:

SQL> exec dbms_stats.gather_table_stats('alex','alex_t02')
SQL> exec dbms_stats.gather_index_stats('alex','pk_alex_t02')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t02_ab')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t02_c')

不进行排序操作,先来对比一下全表扫描和索引扫描的输出结果。如下例所示,可以看到,索引扫描输出的结果是有序的(对c列采用默认的升序排列),全表扫描则是无序的(其结果的顺序为数据实际入库的顺序)。

SQL> select /*+full(alex_t02)*/ id, c
2  from alex_t02 where c<3;
         ID            C
---------- ----------
      17804            1
      18492            2
      48294            2
      52047            1
      36076            2
      79572            1
      96838            2
      86147            2
      89903            2
      90324            1
      92069            1

11 rows selected.

SQL> select /*+index(alex_t02 idx_alex_t02_c)*/ id, c
2  from alex_t02 where c<3;
         ID            C
---------- ----------
      17804            1
      52047            1
      79572            1
      90324            1
      92069            1
      18492            2
      48294            2
      36076            2
      96838            2
      86147            2
      89903            2

11 rows selected.

 

如果需要全表扫描的输出与索引扫描一致,就需要进行一次额外的排序(order by c)操作,如下所示,在SQL语句执行的统计信息中多了一次内存排序操作。

SQL> select /*+full(alex_t02)*/ id, c
   2 from alex_t02 where c<3 order by c;

Statistics
-----------------------------------
          1  sorts (memory)
          0  sorts (disk)
         11  rows processed

如果我们使用的是索引范围扫描的话,即使我们添加了order by子句,声明需要排序操作,但是CBO优化器也会忽略掉这个要求,在执行后的统计信息中也没有出现任何排序,如下所示:

SQL> select /*+index_rs(alex_t02 idx_alex_t02_c)*/ id, c
   2 from alex_t02 where c<3 order by c;
Statistics
-----------------------------------
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

注意

在索引扫描取数的时候,对索引列进行ORDER BY是没有必要的(索引快速全扫描除外)。

2.  索引跳跃扫描排序

前面介绍过索引跳跃扫描其实是将复合索引打散成逻辑上的子索引,再进行扫描。基于这个原理,我们可以想象到,子索引和子索引之间关系是按照复合索引的前导列的顺序有序组织的,子索引内部则是按照子索引的键值的顺序有序组织的。各个子索引对应输出的子结果集也具有同样的顺序关系。

在下面的例子中,order by子句要求的排序方式为(a, b),这和复合索引的默认顺序是一致的,所以子结果集直接输出即可,不必额外的排序,最终看到的统计信息中的排序次数为0。

SQL> select /*+ index_ss(alex_t02 idx_alex_t02_ab) */
2  a, b, name from alex_t02 where b<600 order by a, b;
Statistics
-----------------------------------
          0  sorts (memory)
          0  sorts (disk)
       3003  rows processed

如果order by子句要求的排序方式和复合索引结构的默认顺序不一致呢?如下例所示,b列的数据是随机生成的,本是无序的。在各个子结果集内部,由于索引有序结构的关系,b列是有序排列的,但在各个子结果集之间比较,b列则是无序的。比如:子结果集(a=0)中可能存在b={1, 2,3,4},子结果集(a=1)中可能存在b={1,3,5,6},如果按照复合索引的默认顺序(order by a, b)输出则是b={1,2,3,4,1,3,5,6},如果按照子索引键值顺序(order by b)输出,则是b={1,1,2,3,3,4,5,6},后者的输出意味着需要一次额外的排序。

SQL> select /*+ index_ss(alex_t02 idx_alex_t02_ab) */
2  a, b, name from alex_t02 where b<600 order by b;
Statistics
-----------------------------------
          1  sorts (memory)
          0  sorts (disk)
3003  rows processed

3.  索引快速全扫描排序

现在我们知道了对于复合索引来说,order by子句要求排序的列正好在复合索引键上是可以避免不必要的排序,但是这里也有一个例外,就是索引快速全扫描的情况。前面提到该扫描方式是不支持排序输出的,如果指明order by的话,会需要额外的排序操作,而且此排序的COST开销非常之大,如下例所示:

SQL> select /*+index_ffs(alex_t02 idx_alex_t02_ab)*/ a, b
2  from alex_t02 where b>600 order by a,b;
--------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 97005 |   757K|       |   423   (2)|
|   1 |  SORT ORDER BY        |                 | 97005 |   757K|  1536K|   423   (2)|
|*  2 |   INDEX FAST FULL SCAN| IDX_ALEX_T02_AB | 97005 |   757K|       |    66   (2)|
--------------------------------------------------------------------------------------

Statistics
-----------------------------------
          1  sorts (memory)
          0  sorts (disk)
      96993  rows processed

细心的读者可能已经注意到,上例中添加了HINT关键字强制执行计划,而且执行计划中的COST开销是比较大的,如果去掉HINT呢?结果如下:

SQL> select a, b from alex_t02 where b>600 order by a,b;
-------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 | 97005 |   757K|   292   (1)|
|*  1 |  INDEX FULL SCAN | IDX_ALEX_T02_AB | 97005 |   757K|   292   (1)|
-------------------------------------------------------------------------

Statistics
-----------------------------------
          0  sorts (memory)
          0  sorts (disk)
      96993  rows processed

从执行计划中可以看到,快速全扫描被全扫描代替了,COST开销反而降低了。一般情况下,快速全扫描的效率是要比全扫描高的,此时CBO为什么反而选择全扫描的方式呢?原因就出在排序上。

注意:

对于大结果集的输出,尽量避免不必要的排序,如果能用索引排序进行优化,也请尽量使用。

Trackback

no comment untill now

Add your comment now

切换到手机版