ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 函数索引的使用细节——常数表达式

函数索引的使用细节——常数表达式

原创 Linux操作系统 作者:realkid4 时间:2011-06-14 19:56:54 0 删除 编辑

 

在笔者之前的Blog中,已经多次对函数索引Functional Index进行介绍。其中包括函数索引的原理、使用和应用场景。针对两个使用细节,补充一下这个知识点。

 

 

SQL语句是一种描述性语言,我们只需要进行数据描述,数据库系统DBMS就会生成执行计划路径和操作。同样的数据操作需求,SQL语句书写可能会有一定的差异。

 

DBMSSQL引擎在处理SQL语句之前,通常都会进行一些改写处理。如表达式计算、谓词推进和视图化等等。其中要注意表达式计算的一个特性:“改写运算是不会跨过等号”的。下面通过一个实例来观察:

 

首先还是环境准备:

 

 

SQL> desc t;

Name           Type          Nullable Default Comments

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

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                         

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

(篇幅原因,无关内容省略……

 

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

 

 

我们先观察条件“object_id<1000/5”的情况。

 

 

SQL> explain plan for select * from t where object_id<1000/5;

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 514881935

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

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

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

|   0 | SELECT STATEMENT            |          |   179 | 17363 |     7   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |   179 | 17363 |     7   (0)| 00

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |   179 |       |     2   (0)| 00

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"<200)

 

14 rows selected

 

 

注意上面实验的两个细节。首先,SQL引擎在处理语句形成执行计划之前,1000/5的常数运算条件已经被处理计算。输入到优化器中的SQL命令就是200。其次就是该SQL语句顺利的执行了索引路径。

 

下面我们对该SQL进行简单的改写,而且是等价运算改写。

 

 

SQL> explain plan for select * from t where object_id*5<1000;

Explained

 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |  3614 |   342K|   281   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |  3614 |   342K|   281   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"*5<1000)

 

13 rows selected

 

 

数学上的等价运算并不意味着Oracle SQL优化方案的等价。从filter(“object_id*5<1000”)就可以看出,SQL改写过程并没有智慧的将5转移到等号右边。而是保留在左侧。

 

随着带来的副作用是,原有的索引路径不能使用,取代的是全表扫描。

 

解决该方法的思路有两个,一个就是改写源代码,将“*5”变化为“/5”,该方法是上策。不会影响到原有的索引等优化方案。但是很多时候,DBA们是不能够拿到和改写源代码的,这时候退而求其次的手段就是使用函数索引functional index

 

函数索引functional index虽然主要针对的是函数,但是针对表达式也是有效的。

 

//添加函数索引

SQL> create index idx_t_fid on t(object_id*5);

Index created

 

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

PL/SQL procedure successfully completed

 

 

此时再次生成执行计划。

 

 

SQL> explain plan for select * from t where object_id*5<1000;

Explained

 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1561469142

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

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

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

|   0 | SELECT STATEMENT            |           |   179 | 18258 |     7   (0)| 0

|   1 |  TABLE ACCESS BY INDEX ROWID| T         |   179 | 18258 |     7   (0)| 0

|*  2 |   INDEX RANGE SCAN          | IDX_T_FID |   179 |       |     2   (0)| 0

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"*5<1000)

14 rows selected

 

 

问题得到了解决,访问方式没有变化,SQL语句相同。Oracle选择了函数索引路径执行。CPU成本从原来的281减少到了7,优化效果明显。

 

根据Oracle优化文档中,对表达式常量计算的解析如下:

 

 

Note: The optimizer does not simplify expressions across comparison operators: In the preceding examples, the optimizer does not simplify the third expression into the second. For this reason, application developers write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns.

 

 

由于优化器对于表达式的简化操作是不能跨过比较运算符(=><等)。所以应用程序的开发者首先要保证书写健康可优化的SQL语句,将常量运算尽可能处理掉。

 

对运维人员来说,如果遇到这类问题,很多时候函数索引可能是最好的可接受方案。

 

 

最后,还是重申一下笔者对函数索引的基本观点。大多数应用函数索引的SQL场景,都可以在开发阶段通过SQL改写、字段分割处理等方法加以回避。从性价比角度看,函数索引不是一个很好的优化方案类型。绝大多数情况下的函数索引使用环节,是在运维领域。

 

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

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7677496