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

Wesley13
• 阅读 769

很久之前的自己写的文章了，拿出来复习一下，O(∩_∩)O哈哈~

在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<trunc(sysdate+1)

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

ss_df**+20>*50，优化处理：ss_df*>**30

'X' || hbs_bh>’X5400021452’，优化处理：hbs_bh>'5400021542'

sk_rq**+5=sysdate，优化处理：sk_rq=sysdate-**5

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

qc_bh || kh_bh='5400250000'，优化处理：qc_bh='5400' and kh_bh='250000'

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

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优化器为啥还去大表中寻找数据呢？

About Me

....................................................................................................................................................

本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

ITPUB BLOG：http://blog.itpub.net/26736162

本文地址：http://blog.itpub.net/26736162/viewspace-1329880/

本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）

QQ：642808185 若加QQ请注明您所正在读的文章标题

【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】

....................................................................................................................................................

拿起手机扫描下边的图片来关注小麦苗的微信公众号：xiaomaimiaolhr,学习最实用的数据库技术。

本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

selectsql优化索引objectoracle索引sql数据库JavaJava
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
4246
0
0
添砖java的啾 添砖java的啾
•
3年前
distinct效率更高还是group by效率更高？
目录00结论01distinct的使用02groupby的使用03distinct和groupby原理04推荐groupby的原因00结论先说大致的结论（完整结论在文末）：在语义相同，有索引的情况下groupby和distinct都能使用索引，效率相同。在语义相同，无索引的情况下：distinct效率高于groupby。原因是di
2744
0
0
helloworld_34035044 helloworld_34035044
•
2年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
1210
0
0
待兔 待兔
•
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1137
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2446
0
0
Easter79 Easter79
•
3年前
sql注入
反引号是个比较特别的字符，下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用，不过使用范围只于表名、数据库名、字段名、起别名这些场景，下面具体说下1）表名payload：select\from\users\whereuser\_id1limit0,1;!(https://o
816
0
0
Wesley13 Wesley13
•
3年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景：表结构：主键（非自增）contentCode（varchar），过滤条件列为updateTime（timeStamp），已经为timestamp建立索引。搜索sql为：SELECTFROMmy_hello_tableWHEREupdat
908
0
0
Wesley13 Wesley13
•
3年前
00：Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork)，斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟，已经成为Web应用程序的首选开发语言。Java是简单易学，完全面向对象，安全可靠，与平台无关的编程语言。
1313
0
0
Wesley13 Wesley13
•
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1586
0
0
Python进阶者 Python进阶者
•
11个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
559
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
22
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？