oracle中like优化的方法

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

常规的写法:

 create table t01 as select * from dba_objects;

SQL> createindext01_01 ont01(object_name);

Indexcreated.

SQL> setautot trace

SQL> select* fromt01 whereobject_name like'%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 byoperation 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*Nettoclient

608  bytes received via SQL*Netfromclient

13  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

176 rowsprocessed

优化后的写法:

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

fromt01, (selectrowid fromt01 whereobject_name like'%EMP%') v01

wheret01.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 FULLSCAN      | T01_01 |  7091 |   242K|   147   (2)| 00:00:02 |

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

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

Predicate Information (identified byoperation 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*Nettoclient

608  bytes received via SQL*Netfromclient

13  SQL*Net roundtripsto/fromclient

0  sorts (memory)

0  sorts (disk)

176 rowsprocessed

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