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<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
  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优化器为啥还去大表中寻找数据呢?
       最新内容请见作者的GitHub页:http://qaseven.github.io/
上一篇:2013-7-17学习作业练习


下一篇:金融科技转型之路——银行核心系统数字化转型