问在Oracle数据库中使用CONTAINS子句进行精确短语搜索

EN

SELECT TestColl.tID, TestMetavalues.*
FROM TestColl, TestMetavalues
WHERE TestColl.tID=TestMetavalues.tID 
AND TestColl.TestFullText LIKE '%My favorite thing%' ;

SELECT TestColl.tID, TestMetavalues.*
FROM TestColl, TestMetavalues
WHERE TestColl.tID = TestMetavalues.tID 
  AND TestFullText = 'My favorite thing';

SQL> create table t ( x varchar2(1000));

Table created.

SQL>
SQL> insert into t values ('These are my medium stuff');

1 row created.

SQL> insert into t values ('These are stuff I hate');

1 row created.

SQL> insert into t values ('These are other things');

1 row created.

SQL> insert into t values ('These are semi FAVORITE things');

1 row created.

SQL> insert into t select * from t;

4 rows created.

SQL> insert into t select * from t;

8 rows created.

SQL> insert into t select * from t;

16 rows created.

SQL> insert into t select * from t;

32 rows created.

SQL> insert into t select * from t;

64 rows created.

SQL> insert into t select * from t;

128 rows created.

SQL> insert into t select * from t;

256 rows created.

SQL> insert into t select * from t;

512 rows created.

SQL> --
SQL> -- our special rows
SQL> --
SQL> insert into t values ('These are a few of My Favorite Things');

1 row created.

SQL> insert into t values ('Some other of My Favorite Things');

1 row created.

SQL>
SQL> create index ix on t (x )
  2  indextype is ctxsys.context;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on explain
SQL> select count(*) from t
  2  where CONTAINS(x,'My Favorite Things') > 0;

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

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2114225437

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |    26 |            |          |
|*  2 |   DOMAIN INDEX   | IX   |   130 |  3380 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("X",'My Favorite Things')>0)

SQL>
SQL> select * from t
  2  where CONTAINS(x,'My Favorite Things') > 0
  3  and x like '%My Favorite Things%';

X
----------------------------------------------------------------------------------------------------
These are a few of My Favorite Things
Some other of My Favorite Things

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1339481741

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     6 |   156 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T    |     6 |   156 |     3   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | IX   |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("X" LIKE '%My Favorite Things%' AND "X" IS NOT NULL)
   2 - access("CTXSYS"."CONTAINS"("X",'My Favorite Things')>0)

SQL>
SQL>