SQL优化中索引列使用函数之灵异事件-阿里云开发者社区

开发者社区> 小麦苗> 正文

SQL优化中索引列使用函数之灵异事件

简介: SQL优化中索引列使用函数之灵异事件     在SQL优化内容中有一种说法说的是避免在索引列上使用函数、运算等操作,否则Oracle优化器将不使用索引而使用全表扫描,但是也有一些例外的情况,今天我们就来看看该灵异事件。
+关注继续查看

SQL优化中索引列使用函数之灵异事件

 

 

SQL优化内容中有一种说法说的是避免在索引列上使用函数、运算等操作,否则Oracle优化器将不使用索引而使用全表扫描,但是也有一些例外的情况,今天我们就来看看该灵异事件。

 

 

一般而言,以下情况都会使Oracle的优化器走全表扫描,举例:

 

1.         substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like 5400%

2.         trunc(sk_rq)=trunc(sysdate) 优化处理:sk_rq>=trunc(sysdate) and sk_rq

3.         进行了显式或隐式的运算的字段不能进行索引,如:

ss_df+20>50,优化处理:ss_df>30

'X' || hbs_bh>’X5400021452’,优化处理:hbs_bh>'5400021542'

 sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5 

4.         条件内包括了多个本表的字段运算时不能进行索引,如:ys_df>cx_df,无法进行优化

qc_bh || kh_bh='5400250000',优化处理:qc_bh='5400' and kh_bh='250000'

5.  避免出现隐式类型转化

hbs_bh=5401002554,优化处理:hbs_bh='5401002554',注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。

 

有一些其它的例外情况,如果select 后边只有索引列且where查询中的索引列含有非空约束的时候,以上规则不适用,如下示例:

 

先给出所有脚本及结论:

drop table t  purge;

Create Table t  nologging As select *  from    dba_objects d ;

create   index ind_objectname on  t(object_name); 

 

select t.object_name from t where t.object_name ='T';        --走索引

select t.object_name from t where UPPER(t.object_name) ='T';       --不走索引

select t.object_name from t where UPPER(t.object_name) ='T' and t.object_name IS NOT NULL ;           --走索引  (INDEX FAST FULL SCAN)

select t.object_name from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;     --走索引  (INDEX FAST FULL SCAN)

select t.object_name,t.owner from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;     --不走索引

 

 

测试代码:

C:\Users\华荣>sqlplus lhr/lhr@orclasm

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11 12 10:52:29 2014

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL>

SQL>

SQL> drop table t  purge;

 

表已删除。

 

SQL> Create Table t  nologging As select *  from    dba_objects d ;

 

表已创建。

 

SQL>  create   index ind_objectname on  t(object_name);

 

索引已创建。

 

 

---- t表所有列均可以为空

 

SQL> desc t

 Name                      Null?    Type

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

 OWNER                               VARCHAR2(30)

 OBJECT_NAME                         VARCHAR2(128)

 SUBOBJECT_NAME                      VARCHAR2(30)

 OBJECT_ID                           NUMBER

 DATA_OBJECT_ID                      NUMBER

 OBJECT_TYPE                         VARCHAR2(19)

 CREATED                             DATE

 LAST_DDL_TIME                       DATE

 TIMESTAMP                           VARCHAR2(19)

 STATUS                              VARCHAR2(7)

 TEMPORARY                           VARCHAR2(1)

 GENERATED                           VARCHAR2(1)

 SECONDARY                           VARCHAR2(1)

 NAMESPACE                           NUMBER

 EDITION_NAME                        VARCHAR2(30)

 

SQL>

 

SQL>  set autotrace traceonly;

SQL>  select t.object_name from t where t.object_name ='T';

 

 

执行计划

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

Plan hash value: 4280870634

 

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

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

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

|   0 | SELECT STATEMENT |                |     1 |    66 |     3   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IND_OBJECTNAME |     1 |    66 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - access("T"."OBJECT_NAME"='T')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL plan baseline "SQL_PLAN_503ygb00mbj6k165e82cd" used for this statement

 

 

统计信息

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

         34  recursive calls

         43  db block gets

        127  consistent gets

        398  physical reads

      15476  redo size

        349  bytes sent via SQL*Net to client

        359  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>  select t.object_name from t where UPPER(t.object_name) ='T';

 

 

执行计划

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

Plan hash value: 1601196873

 

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

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

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

|   0 | SELECT STATEMENT  |      |    12 |   792 |   305   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |    12 |   792 |   305   (1)| 00:00:04 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter(UPPER("T"."OBJECT_NAME")='T')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL plan baseline "SQL_PLAN_9p76pys5gdb2b94ecae5c" used for this statement

 

 

统计信息

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

         29  recursive calls

         43  db block gets

       1209  consistent gets

       1092  physical reads

      15484  redo size

        349  bytes sent via SQL*Net to client

        359  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>  select t.object_name from t where UPPER(t.object_name) ='T' and t.object_name IS NOT NULL ;

 

 

执行计划

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

Plan hash value: 3379870158

 

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

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

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

|   0 | SELECT STATEMENT     |                |    51 |  3366 |   110   (1)| 00:00:02 |

|*  1 |  INDEX FAST FULL SCAN| IND_OBJECTNAME |    51 |  3366 |   110   (1)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")='T')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL plan baseline "SQL_PLAN_czkarb71kthws18b0c28f" used for this statement

 

 

统计信息

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

         29  recursive calls

         43  db block gets

        505  consistent gets

        384  physical reads

      15612  redo size

        349  bytes sent via SQL*Net to client

        359  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>  select t.object_name,t.owner from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;

 

 

执行计划

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

Plan hash value: 1601196873

 

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

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

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

|   0 | SELECT STATEMENT  |      |    51 |  4233 |   304   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |    51 |  4233 |   304   (1)| 00:00:04 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("T"."OBJECT_NAME" IS NOT NULL AND

              UPPER("T"."OBJECT_NAME")||'AAA'='TAAA')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL plan baseline "SQL_PLAN_au9a1c4hwdtb894ecae5c" used for this statement

 

 

统计信息

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

         30  recursive calls

         44  db block gets

       1210  consistent gets

       1091  physical reads

      15748  redo size

        408  bytes sent via SQL*Net to client

        359  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select t.object_name from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;

 

 

执行计划

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

Plan hash value: 3379870158

 

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

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

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

|   0 | SELECT STATEMENT     |                |    51 |  3366 |   110   (1)| 00:00:02 |

|*  1 |  INDEX FAST FULL SCAN| IND_OBJECTNAME |    51 |  3366 |   110   (1)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("T"."OBJECT_NAME" IS NOT NULL AND

              UPPER("T"."OBJECT_NAME")||'AAA'='TAAA')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL plan baseline "SQL_PLAN_1gu36rnh3s2a318b0c28f" used for this statement

 

 

统计信息

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

         28  recursive calls

         44  db block gets

        505  consistent gets

          6  physical reads

      15544  redo size

        349  bytes sent via SQL*Net to client

        359  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

 

 

其实很好理解的,索引可以看成是小表,一般而言索引总是比表本身要小得多,如果select 后需要检索的项目在索引中就可以检索的到那么Oracle优化器为啥还去大表中寻找数据呢?

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
函数返回值是否使用引用类型的问题:理解引用、返回值
  在《对象更有用的玻璃罩——常引用》一文中,介绍了对象作为函数的参数时,推荐使用引用的形式。并且,如果实际参数的值不允许改变时,声明为常引用更佳。   在《第8周-任务1-方案3-复数类中运算符重载(与实数运算)》中,又讨论了一个问题,结论是:在类似复数加法运算符重载这样的函数,形式参数用作为常引用最佳,如: friend Complex operator + (const
1151 0
秘籍:使用函数计算珍藏你喜爱的文章
使用函数计算珍藏你喜爱的文章 1. 背景 对于喜欢阅读博客的程序员来说,是不是常常堆满 tab 标签,密密麻麻的连标题都看不清?不舍得关掉,却又抽不出来时间阅读?又或者阅读过了,想收藏起来供日后查阅? 对这些文章来说,onenote、印象笔记等笔记类软件着实不是一个好的去处。
1840 0
SqlServer性能检测和优化工具使用详细
工具概要         如果你的数据库应用系统中,存在有大量表,视图,索引,触发器,函数,存储过程,sql语句等等,又性能低下,而苦逼的你又要对其优化,那么你该怎么办?哥教你,首先你要知道问题出在哪里?如果想知道问题出在哪里,并且找到他,咱们可以借助本文中要讲述的性能检测工具--sql server profiler(处在sql安装文件--性能工具--sql server profiler)     如果知道啦问题出现在哪里,如果你又是绝世高手,当然可以直中要害,写段代码给处理解决掉,但是如果你不行,你做不到,那么也无所谓,可以借助哥的力量给你解决问题。
930 0
SQL优化之使用正确的去重方法
DISTINCT到底该不该使用
953 0
使用异步事件在后台进行计算并报告进度
演练:实现一个使用后台操作的窗体 Visual Studio 2010     其他版本     此主题尚未评级 - 评价此主题     如果某项操作需要很长的时间才能完成,并且不希望用户界面 (UI) 停止响应或“挂起”,则可以使用 BackgroundWorker 类在另一个线程中执行这种操作。
769 0
winform 使用快捷键来触发按钮事件
WinForm中快捷键与组合按键的设置   第一种方法。。代码复杂,操作简单的快捷键 新建一个空白窗体 每个窗体都有这样3个事件:KeyDown、KeyPress、KeyUp,KeyDown和KeyPress都是按键按下事件,但KeyDown用的是KeyCode跟键盘各个按键相对应,它对应Keys枚举,用起来比较方便;而KeyPress用的是KeyChar,这个就要找ASC II编码了,不方便。
927 0
+关注
小麦苗
网名:小麦苗 | 微信公众号:DB宝 | 11g和12c OCM | 《数据库笔试面试宝典》作者,博客地址:http://blog.itpub.net/26736162/abstract/1/
889
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载