在oracle中对于like操作时,如果是前后都是模糊查询的时候(类似于col01 like ‘%xxx%’)是没有办法用到索引的,这里提供一种对于这种情况的优化思路,主要的思路是把大表变小,把查询的实体表变窄,把需要的数据放到索引里.
常规的写法:
create table t01 as select * from dba_objects;
SQL>createindext01_01ont01(object_name); |
Indexcreated. |
SQL>setautot trace |
SQL>select*fromt01whereobject_namelike'%EMP%'; |
176rowsselected. |
Execution Plan |
---------------------------------------------------------- |
Plan hash value: 3295674804 |
-------------------------------------------------------------------------- |
| Id | Operation |Name|Rows | Bytes | Cost (%CPU)|Time | |
-------------------------------------------------------------------------- |
| 0 |SELECTSTATEMENT | | 7091 | 775K| 522 (1)| 00:00:07 | |
|* 1 | TABLEACCESSFULL| 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, (selectrowidfromt01whereobject_namelike'%EMP%') v01 |
wheret01.rowid = v01.rowid; |
176rowsselected. |
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 | INDEXFASTFULLSCAN | T01_01 | 7091 | 242K| 147 (2)| 00:00:02 | |
| 3 | TABLEACCESSBYUSER 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,用这种方法在表越宽返回记录越少时效果越好.