oracle模糊匹配优化,oracle 模糊查询%abc% 优化

在本地测试结果跟楼主的不同啊？

好像效率并没有很大的变化。

create table t1 as select * from dba_objects;

create index idx_object_name on t1(object_name);

insert into t1 select * from t1;

insert into t1 select * from t1;

......................

环境：

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from dba_objects;

COUNT(*)

----------

49864

SQL> select count(*) from t1;

COUNT(*)

----------

1595616

结果：

SQL> select * from t1 where object_name like '%DUAL%';

64 rows selected.

Elapsed: 00:00:01.96

Execution Plan

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

Plan hash value: 3617692013

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

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

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

|   0 | SELECT STATEMENT  |      |   120 | 21240 |  3629   (1)| 00:00:51 |

|*  1 |  TABLE ACCESS FULL| T1   |   120 | 21240 |  3629   (1)| 00:00:51 |

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

Predicate Information (identified by operation id):

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

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

0  recursive calls

0  db block gets

10928  consistent gets

0  physical reads

0  redo size

3313  bytes sent via SQL*Net to client

429  bytes received via SQL*Net from client

6  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

64  rows processed

SQL> select /*+index(t1 idx_object_name)*/* from t1 where object_name like '%DUAL%';

64 rows selected.

Elapsed: 00:00:02.03

Execution Plan

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

Plan hash value: 2926915253

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

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

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

|   0 | SELECT STATEMENT            |                 |   120 | 21240 |  1170   (1)| 00:00:17 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1              |   120 | 21240 |  1170   (1)| 00:00:17 |

|*  2 |   INDEX FULL SCAN           | IDX_OBJECT_NAME | 67231 |       |   116   (5)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

0  recursive calls

0  db block gets

4247  consistent gets

0  physical reads

0  redo size

3313  bytes sent via SQL*Net to client

429  bytes received via SQL*Net from client

6  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

64  rows processed

SQL> select * from t1 where rowid in (select rowid from t1 where object_name like '%DUAL%');

64 rows selected.

Elapsed: 00:00:01.98

Execution Plan

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

Plan hash value: 754227648

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

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

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

|   0 | SELECT STATEMENT            |                 |   120 | 32040 |  1541   (1)| 00:00:22 |

|   1 |  NESTED LOOPS               |                 |   120 | 32040 |  1541   (1)| 00:00:22 |

|*  2 |   INDEX FAST FULL SCAN      | IDX_OBJECT_NAME |   120 |  9360 |  1421   (1)| 00:00:20 |

|   3 |   TABLE ACCESS BY USER ROWID| T1              |     1 |   189 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

0  recursive calls

0  db block gets

4368  consistent gets

0  physical reads

0  redo size

3313  bytes sent via SQL*Net to client

429  bytes received via SQL*Net from client

6  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

64  rows processed