dingjun123的亚虎娱乐博客

Focus on Oracle,Oracle ACE Associate

  • 博客访问: 510305
  • 亚虎娱乐数量: 42
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2213
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
文章分类

全部亚虎娱乐(42)

文章存档

2014年(3)

2013年(39)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅

分类: Oracle

      【Oracle index】SQL语句无法走索引的一些情况分析及语句改写思路 这里列出了一些SQL语句无法利用现有索引的情况,特别是不好的SQL写法,导致对列进行了相关运算,比如数学运算col+5,函数运算to_char(done_date,'yyyy-mm-dd')等等,导致索引失效。当然,解决此类问题的最好办法,是避免对需要使用索引的列进行相关运算,而保持列最原始的状态(在索引创建未使用函数情况下)。但是,函数索引也是非常常见的。
 
    
函数索引(FBI)还是很常见的,比如create index idx_t on t(to_char(done_date,'yyyy-mm-dd'));是函数索引,create index idx_t on t(status,0)是函数索引(这个较特别,使用一个常数伪列,在需要将NULL存储到BTREE INDEX中使用)。函数索引有一点要特别注意,在使用函数索引的时候,SQL语句中的对应表达式必须与创建函数索引的表达式完全一致(当然,空格、关键字大小写的可以忽略),如果不是完全一致,则也利用不上函数索引。下面举两个例子说明:
1.创建CASE WHEN函数索引
 比如表t,其中status有2个值,VALID和INVALID表示状态,其中INVALID的占比较少,为了减少索引的存储空间,可以将status='INVALID'的纳入到索引中,这时候就需要使用case when或decode...
dingjun123@ORADB> CREATE TABLE t
  2  AS
  3  SELECT * FROM dba_objects;
Table created.
Elapsed: 00:00:00.88


dingjun123@ORADB> CREATE INDEX IDX_T ON T(CASE WHEN STATUS='INVALID' THEN 1 ELSE NULL END);
Index created.
Elapsed: 00:00:01.18


dingjun123@ORADB> set autotrace traceonly exp

--和建立函数索引的表达式一致,可以使用索引
dingjun123@ORADB> SELECT *
  2  FROM t
  3  WHERE CASE WHEN STATUS = 'INVALID'
  4                       THEN 1
  5                       ELSE NULL
  6                       END = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1051 |   100K|   146   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1051 |   100K|   146   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |  1051 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "STATUS" WHEN 'INVALID' THEN 1 ELSE NULL END =1)

--和建立函数索引的表达式不一致,索引失效,少了ELSE NULL
dingjun123@ORADB> SELECT *
  2  FROM t
  3  WHERE CASE WHEN STATUS = 'INVALID'
  4                       THEN 1
  5                       END = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   753 | 73794 |   302   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |   753 | 73794 |   302   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(CASE "STATUS" WHEN 'INVALID' THEN 1 END =1)


    建立这样的函数索引是常见的,但是可能出现这样的情况:要么建立索引的时候没有加ELSE NULL,或者加了,但是在写语句的时候有的加了ELSE NULL,有的未加,导致函数索引表达式与SQL语句里的表达式不完全一致,导致索引失效。所以,有必要对函数索引的创建以及SQL语句使用函数索引,建立规范,特别是要严格测试。

2.对日期列建立函数索引
dingjun123@ORADB> drop table t;
Table dropped.
Elapsed: 00:00:00.25

dingjun123@ORADB> CREATE TABLE t
  2  AS
  3  SELECT * FROM dba_objects;
Table created.
Elapsed: 00:00:00.84

dingjun123@ORADB> CREATE INDEX idx_t ON t(TO_CHAR(last_ddl_time,'YYYY-MM-DD'));
Index created.
Elapsed: 00:00:00.68


dingjun123@ORADB> exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'T',estimate_percent => 100,cascade => TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.96

--语句中的fmt大小写与索引创建的表达式fmt不一样,导致走不了索引
dingjun123@ORADB> SELECT *
  2  FROM t
  3  WHERE TO_CHAR(last_ddl_time,'yyyy-mm-dd')='2013-5-20';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   753 | 81324 |   303   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |   753 | 81324 |   303   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("LAST_DDL_TIME"),'yyyy-mm-dd')='
              2013-5-20')


--完全一样,走索引
dingjun123@ORADB> SELECT *
  2  FROM t
  3  WHERE TO_CHAR(last_ddl_time,'YYYY-MM-DD')='2013-5-20';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   291 | 31428 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   291 | 31428 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |   291 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(TO_CHAR(INTERNAL_FUNCTION("LAST_DDL_TIME"),'YYYY-MM-DD')='2013-
              5-20')

   第2个例子,很多情况下是不需要使用函数索引的,可以用where last_ddl_time>=to_date... and last_ddl_time

阅读(4933) | 评论(1) | 转发(2) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册