关于like的优化思考

原贴地址：http://www.itpub.net/forum.php?mod=viewthread&tid=1702729

在项目中使用oracle数据库，有着两个既是难点也是重点的两条要求，就是数据的准确性以及数据库的性能优化。所以在保证数据一致准确的前提下对database的优化就成为了oracle数据库的一个永恒的主题。
　　资深的Oracle数据库DBA通常会要求提出性能问题的人对数据库做一个statspack，贴出数据库配置，还有的DBA要求抓出执行最慢的语句的执行计划来进行sql分析。这些都是常用的优化手段。但是根据这些手段只能分析出哪里出现了问题，或者哪条sql出现性能故障，却依然手足无措，其实Oracle SQL 调整是 Oracle 性能调优中最重要的领域之一，只要通过一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能，这是一点都不奇怪的。对于存在like关键字通配符的模糊查寻的sql 语句往往令developer感到头疼，也许一条简单的sql模糊查询在千万行级别的表中就会执行的很慢，大大的影响了系统的性能，下面针对like的sql语句优化有几点小的建议。
   例如：select * from employee where last_name like '%c%；
这样的查询语句在执行的过程中很慢，但是业务中需要有模糊查询的要求，又不得不用like
1. 分析业务需求是否前后都必须是模糊查询如果可以改写成通配符在后面，原因是%XXX%这样的字段使用不到该列的索引，根据CBO规则很可能走的是FULL ACCESS TABLE，而XXX%这样的字段却可以走索引，会加快执行速度。如：select * from employee where last_name like 'c%' 

2. 如果业务需求不能前置确定的变量XXX，却可以写成%XXX，虽然这样依然不能够走索引，但是可以通过翻转函数索引来达到优化的目的。如：select * from employee where reverse(last_name) like reverse('%c'); 当然，在这里值得注意的是这个反转索引不是真正意义上的反转索引，只不过是一个反转函数的使用，真正意义上的反转索引是主要防止热块的出现而将索引数据防止在不同的索引数据块中，也就是在索引中使本来连续的值不连续的分布，可以说在频繁的插入的情况下可以减小数据块竞争，但缺点也十分明显，就是在范围 scan的时候就用不上了。

3. 如果业务中必须使用类似%XXX%类似的sql查询，也有几个方案可供选择。
（1）可以采用hint的强制索引，但是优化成功率比较低，根据CBO的优化器计算出的方式可能更加符合性能，但是也不失一种方法所以总结在这里。如：Select /*+INDEX (employee pk_last_name)*/  * from employee where last_name like 'c%' ;
（2）可以采用instr或其他取得XXX字符或者字符位置的函数来代替like的使用达到同样的业务需求。如：select count(*) from employee where instr (last_name,'c') > 0;
（3）如果该表需要被多次访问而且不经常对该表进行添加删改等DML语句操作可以建立物化视图增加查询速度。
（4）如果该表有明显可以分区的字段，比如日期，年份，账期等有关时间的字段，可以建立分区表，考虑从表结构的更改入手调整性能，这种优化多见于数据仓库。
（5）利用并行sql技术，充分利用多核CPU性能来完成sql的查询。如：SELECT /*+PARALLEL(employee,2)*/ * FROM employee WHERE last_name LIKE '%c%' ;
（6）可以采用全文索引技术来实现该字段的全文索引，但是数据精确度可能会有所损失。
（7）最后一个不是方法的方法就是把该字段数据使用其他的技术来实现，而不是依赖于oracle数据库，如果数据比较多的话可以使用基于java的lucene全文搜索引擎技术来实现，但对于架构来讲会增加很大的困难以及增加结构的复杂性，强烈建议不适用这种方法。

原贴地址：http://woodbow.net/91.html

oracle中like优化的一种方法

作者: woodbow日期: 2012 年 12 月 5 日 发表评论 (0)查看评论

在oracle中对于like操作时,如果是前后都是模糊查询的时候(类似于col01 like ‘%xxx%’)是没有办法用到索引的,这里提供一种对于这种情况的优化思路,主要的思路是把大表变小,把查询的实体表变窄,把需要的数据放到索引里.

常规的写法:

SQL> createindex t01_01 on t01(object_name);

 

Index created.

 

SQL> setautot trace

SQL> select* from t01 where object_namelike '%EMP%';

 

176 rowsselected.

 

Execution Plan

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

Plan hash value: 3295674804

 

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

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

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

|   0 | SELECTSTATEMENT  |      |  7091 |   775K|   522   (1)| 00:00:07 |

|*  1 |  TABLEACCESS FULL| T01  |  7091 |   775K|   522   (1)| 00:00:07 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_NAME"LIKE '%EMP%')

 

Statistics

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

          1  recursive calls

          0  db block gets

       2335  consistent gets

          0  physical reads

          0  redosize

      14552  bytes sent via SQL*Netto client

        608  bytes received via SQL*Netfrom client

         13  SQL*Net roundtripsto/fromclient

          0  sorts (memory)

          0  sorts (disk)

        176 rows processed

优化后的写法:

SQL> select/*+ use_nl(t01 v01) */ *

  2    from t01, (selectrowid from t01 where object_namelike '%EMP%') v01

  3   where t01.rowid = v01.rowid;

 

176 rowsselected.

 

Execution Plan

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

Plan hash value: 73453348

 

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

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

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

|   0 | SELECTSTATEMENT            |        |  7091 |  1017K|  7241   (1)| 00:01:27 |

|   1 |  NESTED LOOPS               |        |  7091 |  1017K|  7241   (1)| 00:01:27 |

|*  2 |   INDEXFAST FULL SCAN      | T01_01 |  7091 |   242K|   147   (2)| 00:00:02 |

|   3 |   TABLEACCESS BY USER ROWID| T01    |     1 |   112 |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OBJECT_NAME"LIKE '%EMP%')

 

Statistics

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

          1  recursive calls

          0  db block gets

        857  consistent gets

          0  physical reads

          0  redosize

      18049  bytes sent via SQL*Netto client

        608  bytes received via SQL*Netfrom client

         13  SQL*Net roundtripsto/fromclient

          0  sorts (memory)

          0  sorts (disk)

        176 rows processed

 针对于上面的例子,IO从2335降到857,用这种方法在表越宽返回记录越少时效果越好.