2.2.3 降序索引

看到这里,有的读者可能会问到,我们一直都是在说升序的情况,如果要求降序查询呢,情况是否会不一样呢?是的,会不一样。我们接下来将围绕这个索引降序的话题来说一说。

我们可以从单列索引和复合索引两个维度来进行阐述。当然,在阐述之前,先介绍一个新的概念,就是“降序索引”。

1.  降序索引

什么是降序索引呢?其实降序索引也是B树索引,只是将通常意义上的B树索引中的存储方式从升序变成了降序,如果应用场景中经常会出现降序排序的查询,建立一个降序B树索引不失一个很好的选择。

默认情况下建立的B树索引均为升序(ASC)索引,语句如下:

SQL> create index idx_alex_t02_c on alex_t02 (c);

如果需要建立降序(DESC)索引,只需要在索引列后注明“desc”关键字,语句如下:

SQL> create index idx_alex_t02_c on alex_t02 (c desc);

2.  单列索引的降序

对于单列索引来说,进行升序查询的时候,其实就是按照从左到右的顺序逐一扫描索引的叶节点块。这个动作是否可以反过来进行呢,从右到左地去扫描?答案是肯定的,这样就是降序查询的优化了,即使我们建索引的时候,是按照升序来建的,CBO优化器也可以自动进行降序查询优化。

下面是一个降序查询的例子,在升序索引上的扫描,不论是升序查询,还是降序查询,都没有额外的排序操作,而且CBO计算出来的COST开销也是一致的。

SQL> select id, name from alex_t02 where c<=6 order by c;
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     5 |   105 |     8   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T02       |     5 |   105 |     8   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T02_C |     5 |       |     2   (0)|
-----------------------------------------------------------------------------------

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

SQL> select id, name from alex_t02 where c<=6 order by c desc;
------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     5 |   105 |     8   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID | ALEX_T02       |     5 |   105 |     8   (0)|
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ALEX_T02_C |     5 |       |     2   (0)|
------------------------------------------------------------------------------------

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

同理可知,如果我们建立的是降序索引,CBO优化器也会自动对升序查询进行优化,同样不会有额外的COST开销。但是,降序索引上的升序扫描会被识别为降序操作。

提示

所以,对于单列索引来说,没有必要刻意创建降序索引,默认的升序索引是可以支持降序查询的,并且自动优化,不会产生额外的COST开销。

3.  复合索引的降序

如果说升序索引上进行降序查询可以自动优化,那还需要降序索引干什么呢?它的作用主要表现在复合索引的应用上。

我们下面通过几个例子来分析一下其应用场景,先清理掉之前的索引,在(b, c)列上创建一个复合索引。SQL语句如下所示:

SQL> drop index idx_alex_t02_ab;
SQL> drop index idx_alex_t02_c;
SQL> create index idx_alex_t02_bc on alex_t02 (b, c);
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t02_bc')

此时,在(b, c)列上进行(b asc, c asc)的排序操作,很显然是不会有额外排序的,复合索引两个索引列都是升序组织起来的。示例如下:

SQL> select id, name from alex_t02
2  where b<=400 and c<=400 order by b, c;
------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    40 |  1040 |    47   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T02        |    40 |  1040 |    47   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T02_BC |    40 |       |     7   (0)|
------------------------------------------------------------------------------------

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

将排序条件修改一下,(b desc, c asc),b列降序,c列升序。先来想象一下,此刻b列需要降序,CBO优化器可以自动优化,让b列的从大到小地读,也就是叶节点块从右往左地扫描,这没有问题。但不幸地是,c列则需要从左往右地扫描,这就发生矛盾了。解决矛盾的方式就是额外进行一次排序,示例如下所示:

SQL> select id, name from alex_t02
2   where b<=400 and c<=400 order by b desc, c;
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |    40 |  1040 |    48   (3)|
|   1 |  SORT ORDER BY               |                 |    40 |  1040 |    48   (3)|
|   2 |   TABLE ACCESS BY INDEX ROWID| ALEX_T02        |    40 |  1040 |    47   (0)|
|*  3 |    INDEX RANGE SCAN          | IDX_ALEX_T02_BC |    40 |       |     7   (0)|
-------------------------------------------------------------------------------------

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

我们说一切从适合应用场景出发,如果应用场景中,经常要求(b desc, c asc)的排序方式,那我们就应该创建一个如下的降序索引:

SQL> drop index idx_alex_t02_bc;
SQL> create index idx_alex_t02_bc on alex_t02 (b desc, c);
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t02_bc')

新的索引创建完成,再来执行一下上面的两个SQL语句看看。此时的情况完全反过来了,(b desc, c asc)没有额外排序,而(b asc, c asc)有了。

SQL> select id, name from alex_t02
2  where b<=400 and c<=400 order by b, c;

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

SQL> select id, name from alex_t02
2   where b<=400 and c<=400 order by b desc, c;

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

此处,我们不妨再引申一下。现在的索引顺序是(b desc, c asc),如果SQL语句要求的排序输出的方式变为(b asc, c desc)呢?如果你能像刚才一样想象一下,就不难得到答案。此时,b列和c列都是要求从右往左地扫描索引叶节点块,CBO优化器可以通过INDEX RANGE SCAN DESCENDING的方式优化执行计划,其过程不会有额外的排序和COST开销。示例如下所示:

SQL> select id, name from alex_t02
2  where b<=400 and c<=400 order by b, c desc;
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |    40 |  1040 |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID | ALEX_T02        |    40 |  1040 |     5   (0)|
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ALEX_T02_BC |     1 |       |     4   (0)|
-------------------------------------------------------------------------------------

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

降序索引在日常工作中尤其是做报表和大数据分析应用的SQL语句,应该能发挥比较积极和重要的作用。INDEX RANGE SCAN DESCENDING的扫描优化方式也不能忽略,这种优化方式对于INDEX FULL SCAN同样适用。

提示

降序索引和降序扫描在索引的排序应用中,有积极的意义的。根据实际应用情况,选择合适的索引组织形式,往往可以事半功倍的。

Trackback

no comment untill now

Sorry, comments closed.

切换到手机版