oracle全文索引慢,关于 全文索引和like快慢的问题，请教各位

本帖最后由 tolilong 于 2012-8-23 10:24 编辑

关于 全文索引和like快慢的问题，请教各位？？？

SQL> select count(*) from tt;

COUNT(*)----------

1205904

tt表的数据为1205904记录，全文索引的时间为9.51s，like用了5.26。怎么全文索引不快，反而变慢了。

如下为两条sql的explain。

SQL> set autotrace traceonly

SQL> select * from tt where contains(name,'龙')>0;

14700 rows selected.

Elapsed: 00:00:09.51

Execution Plan

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

Plan hash value: 3770467722

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

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

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

|   0 | SELECT STATEMENT            |          |  6380 |  1196K|  2469   (1)| 00:00:30 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TT       |  6380 |  1196K|  2469   (1)| 00:00:30 |

|*  2 |   DOMAIN INDEX              | TT_INDEX |       |       |  1070   (0)| 00:00:13 |

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

Predicate Information (identified by operation id):

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

2 - access("CTXSYS"."CONTAINS"("NAME",'龙')>0)

Statistics

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

15  recursive calls

0  db block gets

17889  consistent gets

26338  physical reads

0  redo size

2955486  bytes sent via SQL*Net to client

11292  bytes received via SQL*Net from client

981  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

14700  rows processed

SQL> select * from tt where name like '%龙%';

14700 rows selected.

Elapsed: 00:00:05.26

Execution Plan

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

Plan hash value: 264906180

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

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

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

|   0 | SELECT STATEMENT  |      | 60295 |    11M|  8793   (1)| 00:01:46 |

|*  1 |  TABLE ACCESS FULL| TT   | 60295 |    11M|  8793   (1)| 00:01:46 |

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

Predicate Information (identified by operation id):

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

1 - filter("NAME" LIKE '%龙%')

Statistics

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

2  recursive calls

0  db block gets

33022  consistent gets

10142  physical reads

0  redo size

2220709  bytes sent via SQL*Net to client

11292  bytes received via SQL*Net from client

981  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

14700  rows processed