ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引优化执行路径两例

索引优化执行路径两例

原创 Linux操作系统 作者:realkid4 时间:2011-05-19 20:45:32 0 删除 编辑

 

随着CBO时代的到来,Oracle优化器越来越智能,生成的执行计划也是越来越巧妙。一些与传统方式路径用法,在新引入的执行计划中得到了体现。

 

 

索引Index的引入目的就是提高数据检索速度,减少逻辑物理IO读取。最常见的索引使用场景就是在where条件后出现的数据列,如果有索引,适当情况下就可以执行索引路径。此外,在一些特殊的SQL语句中,虽然不是常规的用途,索引也是可以发挥卓越的作用。本篇就是列举两个场景,描述借助索引构造出的高效执行计划。

 

1.       环境准备

 

首先我们准备一下环境。

 

//选择Oracle 11g R2

SQL> select * from v$version where rownum<2;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

 

SQL> create table t as select * from dba_objects;

Table created

 

//构建索引;

SQL> create index idx_t_id on t(object_id);

Index created

 

//收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

 

2.       获取最大max值路径

 

我们通常进行max/min极值定位的时候,使用max/min函数。下面我们来查看这个执行计划。

 

SQL> explain plan for select max(object_id) from t;

已解释。

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------

Plan hash value: 2448092560

 

---------------------------------------------------------------------------------------

| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |          |     1 |     5 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |          |     1 |     5 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_T_ID |     1 |     5 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

 

已选择9行。

 

 

此处我们的SQL语句中,并没有出现where条件。如果在RBO时代,这样的场景一定是进行全表扫描。而此处Oracle选择了索引对象路径,执行Index full scan(MIN/MAX)操作。

 

 

这种方式路径的选择就是CBO智能化的一种体现。理解起来也不难。索引的结构本质上就是将索引列所有的取值,经过排序之后,作为叶子节点的一颗B*树。叶子节点分别是索引列值和对应的rowid信息。借助这样的结构,如果可以直接找到索引树叶子节点的两端,就可以直接获取到索引列的取值。而不需要访问数据表segment对象。

 

 

优秀巧妙的执行计划,建立在对数据表信息的完全掌握(统计信息)和丰富的内置操作选项(执行计划中每步的操作)上。“INDEX FULL SCAN (MIN/MAX)”就是按照索引叶子顺序扫描,获取两端最大或者最小值的操作选项。

 

 

那么,如果我们同时选择获取最大和最小值,执行路径是如何呢?

 

 

SQL> explain plan for select max(object_id),min(object_id) from t;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     5 |   282   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |

|   2 |   TABLE ACCESS FULL| T    | 72583 |   354K|   282   (1)| 00:00:04 |

---------------------------------------------------------------------------

已选择9行。

 

 

 

此时,Oracle放弃了路径。那么,如何让这个SQL也提高执行操作呢?

 

 

3.       Count涉及的索引路径

 

当我们进行count操作时,是否可以借助索引的力量呢?因为检索索引叶子节点的IO读取毕竟要小于进行全表扫描。

 

 

SQL> explain plan for select count(*) from t;

已解释。

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------

Plan hash value: 2966233522

-------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |   282   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T    | 72583 |   282   (1)| 00:00:04 |

-------------------------------------------------------------------

已选择9行。

 

 

此处我们失算了,Oracle不认为检索索引树可以获取全部行数。那么,如何让Oracle认为读取索引树一定可以获取到全部数据行数呢?其中的障碍,想必就是空值null的问题。

 

 

我们选择的列object_id,是一个可以为空的数据列。Oracle认为该列中可以出现空值,而空值是不会进入索引树结构的。所以,检索索引树叶子节点,也许会落下那些空值。于是,Oracle决定保险的进行全表扫描。

 

 

那么,我们如何处理呢?就是要告诉CBO,说这个表的所有行都出现在叶子节点上,不会有落下的。

 

//修改属性列属性

SQL> alter table t modify object_id not null;

Table altered

 

//重新收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

此时,进行执行路径获取。

 

 

SQL> explain plan for select count(*) from t;

已解释。

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

Plan hash value: 3570898368

--------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     1 |    45   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |          |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| IDX_T_ID | 72582 |    45   (0)| 00:00:01 |

--------------------------------------------------------------------------

已选择9行。

 

 

 

果然,我们提示告诉Oracle该列的数据信息之后,CBO智能的得出了我们希望的执行路径。

 

此时,我们重新尝试第二部分的那个同时获取maxminSQL语句。

 

 

SQL> explain plan for select min(object_id),max(object_id) from t;

已解释。

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------

Plan hash value: 3570898368

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     1 |     5 |    45   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |          |     1 |     5 |            |          |

|   2 |   INDEX FAST FULL SCAN| IDX_T_ID | 72582 |   354K|    45   (0)| 00:00:01 |

----------------------------------------------------------------------------------

 

已选择9行。

 

 

原来的全表扫描,也在丰富列信息之后发生了优化。

 

 

4.       结论

 

进入CBO时代之后,Oracle生成SQL执行计划的智能化程度越来越高。借助有限的资源,生成高效的执行路径已经成为可能。对我们数据库设计人员和SQL书写人员来说,应该注意几个方面。

 

ü        尽可能丰富对数据信息的描述。本篇的转折点就是对索引列非空的设置。很多时候,开发设计人员对如默认值、非空选项等不是很关注。其实,这些因素不仅仅是一种约束。对CBO而言,也是了解结构、生成更高效执行计划的信息基础。“不是我做不到,你要告诉我可以做到”;

ü        书写谨慎的SQLSQL是一种描述语言,不同的方式可能针对相同的数据需求,对应不同的执行计划。绝对不要将数据库作为一个黑箱,要为每一句SQL负责任。要做到这点,需要了解Oracle执行原理和方法,才能写一手“漂亮”的SQL

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-695791/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7677329