随着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智能的得出了我们希望的执行路径。
此时,我们重新尝试第二部分的那个同时获取max和min的SQL语句。
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而言,也是了解结构、生成更高效执行计划的信息基础。“不是我做不到,你要告诉我可以做到”;
ü 书写谨慎的SQL。SQL是一种描述语言,不同的方式可能针对相同的数据需求,对应不同的执行计划。绝对不要将数据库作为一个黑箱,要为每一句SQL负责任。要做到这点,需要了解Oracle执行原理和方法,才能写一手“漂亮”的SQL;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-695791/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-695791/