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

架构设计
896
SQL优化中索引列使用函数之灵异事件

很久之前的自己写的文章了，拿出来复习一下，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源创计划”，欢迎正在阅读的你也加入，一起分享。

image
平原之胸
对待自己温柔一点。你只不过是宇宙的孩子，与植物、星辰没什么两样。
4
发布数
3
关注者
3167
累计阅读

热门教程文档

Golang
23小节
Typescript
31小节
Maven
5小节
Java
12小节
CSS
33小节
广告
0 1