不要忽略降序索引

我提这个话题主要是为了说明一个排序的道理。很多人可能忽略了sql语句中排序这块,其实很多时刻,避免了排序可以大大提升SQL语句的性能,这是非常重要的一点!现在我来举例说明降序索引避免排序:

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 3月 14 14:26:15 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table ljb_test;
表已删除。
SQL> create table ljb_Test as select * from dba_objects;
表已创建。
SQL> create index idx_ljb_test on ljb_test (owner,object_id);
索引已创建。
SQL> set autot trace exp
设置该列为非空的目的是因为索引不存空值
SQL> alter table ljb_Test modify owner not null;
表已更改。
SQL> set linesize 1000

通过HINT让以下查询走索引,发现有个SORT ORDER BY排序的过程,正常情况下用到索引的语句不要排序,因为索引已经排序过了,但是为什么如下语句还要再排序呢,因为目前的查询是该索引既要从owner列往前读又要从object_type往后读,此是ORACLE只能把所有行数据收集起来,然后排序实现这样了。

SQL> select /*+index(a,idx_ljb_test)*/ * from ljb_test a order by owner desc ,object_type asc;
执行计划
-------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1087399357
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 70371 |    11M|       |  4812   (1)| 00:00:58 |
|   1 |  SORT ORDER BY               |              | 70371 |    11M|    28M|  4812   (1)| 00:00:58 |
|   2 |   TABLE ACCESS BY INDEX ROWID| LJB_TEST     | 70371 |    11M|       |  2070   (1)| 00:00:25 |
|   3 |    INDEX FULL SCAN           | IDX_LJB_TEST | 70371 |       |       |   197   (2)| 00:00:03 |
-------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

那如果使用了如下(owner desc,object_type asc ) desc降序索引后,这个时候索引的存储方式比起原先的存储方式改变了!是以owner照升序存储,以object_type按降序存储,这样查询的时候,owner从索引存储的一边从大读到小,object_type则是从索引的另一边从小读到大!

SQL> drop index idx_ljb_test;
索引已删除。
SQL> create index idx_ljb_test on ljb_test(owner desc,object_type asc);
索引已创建。

现在执行如下,大家可以看到,排序操作消失了!

SQL>  select /*+index(a,idx_ljb_test)*/ * from ljb_test a order by owner desc ,object_type asc;
执行计划
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2893417570
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 70371 |    11M|  2986   (1)| 00:00:36 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST     | 70371 |    11M|  2986   (1)| 00:00:36 |
|   2 |   INDEX FULL SCAN           | IDX_LJB_TEST | 70371 |       |   243   (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------------------------------------
Note
   - dynamic sampling used for this statement

总结:本节有一定的典型性,降序索引在日常工作中尤其是出报表的sql语句,应该能发挥比较重要的作用,要时刻对排序敏感,很多情况下,临时表空间暴涨,查询速度太慢等都是因为排序太多,PGA的排序区装不下,不得不放到临时表空间去排序,临时表空间是磁盘和内存的速度无法比较,当然要慢,而且临时表空间一般不是设置无限扩大的,不好的语句过多过大的排序最终将导致ORACLE报临时表空间不足的错而退出,大家一定也经常有见过临时表空间不足而报的错误 吧。另外
引申联想:本小节实验结束后,忽然想到,其实可以做一下更加深入的研究,大家觉得如果索引是这样建立的create index idx_ljb_test on ljb_test(owner desc,object_type asc);而的查询转变为select /*+index(a,idx_ljb_test)*/ * from ljb_test a order by owner asc ,object_type desc; 也就是将owner的desc转变为asc,将object_type的asc转变为desc,那先前建的这个索引还能避免排序吗?

执行如下语句查询后发现, owner和object_type倒过来后又要使用排序了!

SQL>  select /*+index(a,idx_ljb_test)*/ * from ljb_test a order by owner asc,object_type desc;
执行计划
-------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1087399357
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 68286 |    11M|       |  5647   (1)| 00:01:08 |
|   1 |  SORT ORDER BY               |              | 68286 |    11M|    27M|  5647   (1)| 00:01:08 |
|   2 |   TABLE ACCESS BY INDEX ROWID| LJB_TEST     | 68286 |    11M|       |  2986   (1)| 00:00:36 |
|   3 |    INDEX FULL SCAN           | IDX_LJB_TEST | 68286 |       |       |   243   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

是否就得出结论,遇到这样的排序查询,如果升序和降序的两列倒过来,对应的索引就要重新建,否则还是无法避免排序?
我想很多人都要回答YES了,因为实验的结果都出来了。
不过事实上并非如此,注意到,这个时候索引走的INDEX FULL SCAN,如果改变直接使用降序索引的HINT,会是什么效果呢?

SQL>  select /*+ index_desc(ljb_test)*/ * from ljb_test order by owner asc,object_type desc;
执行计划
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 318971137
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 68286 |    11M|  2986   (1)| 00:00:36 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST     | 68286 |    11M|  2986   (1)| 00:00:36 |
|   2 |   INDEX FULL SCAN DESCENDING| IDX_LJB_TEST | 68286 |       |   243   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------------------------------
Note
   - dynamic sampling used for this statement

吃惊吧,怎么现在又没有排序了,那怎么选择这样的索引方式呢?我的回答是,表和索引有了足够的信息让ORACLE知道,ORACLE在正确的分析下,得出正确的代价后,认为让排序消失的这个INDEX FULL SCAN DESCENDING是合理的,自然就选择了这种方式,于是倒过来升序和降序的查询一样也可以省略了排序。看来INDEX FULL SCAN DESCENDING这样的扫描方式也应该引起的注意!

Trackback

no comment untill now

Add your comment now

切换到手机版