有关oracle中contains的问题。

Question

奇怪，我的测试结果
SQL> select userid,contains(userid,'first') from a;USERID                                             CONTAINS(USERID,'FIRST')
-------------------------------------------------- ------------------------
the first                                                                 8
userid                                                                    0
userid1                                                                   0
first天安门                                                               0
first 天安门                                                              0
the first 天安门                                                          0
userid2                                                                   0
userid                                                                    0已选择8行。SQL>

Answer 1

SQL> select userid,contains(userid,'first') from a;USERID                                             CONTAINS(USERID,'FIRST')
-------------------------------------------------- ------------------------
the first one                                                             0
the first                                                                 8
userid                                                                    0
userid1                                                                   0
first天安门                                                               0
first 天安门                                                              0
the first 天安门                                                          0
userid2                                                                   0
userid                                                                    0已选择9行。SQL> select userid,contains(userid,'天安门') from a;USERID                                             CONTAINS(USERID,'天安门')
-------------------------------------------------- -------------------------
the first one                                                              0
the first                                                                  0
userid                                                                     0
userid1                                                                    0
first天安门                                                                0
first 天安门                                                               0
the first 天安门                                                           0
userid2                                                                    0
userid                                                                     0已选择9行。SQL> select userid,contains(userid,'one') from a;USERID                                             CONTAINS(USERID,'ONE')
-------------------------------------------------- ----------------------
the first one                                                           0
the first                                                               0
userid                                                                  0
userid1                                                                 0
first天安门                                                             0
first 天安门                                                            0
the first 天安门                                                        0
userid2                                                                 0
userid                                                                  0已选择9行。SQL>

Answer 2

楼主的contains('beijing天安门', '天安门')>0怎么可以的？
关注！！！

Answer 3

Ora92@test>select contains(c,'0') from aab;
select contains(c,'0') from aab
*
ERROR 位于第 1 行:
ORA-20000: Oracle Text error:
DRG-10599: 列没有编制索引
已用时间:  00: 00: 06.02
Ora92@test>

Answer 4

Ora92@test>select contains(c,'0') from aab;
select contains(c,'0') from aab
*
ERROR 位于第 1 行:
ORA-20000: Oracle Text error:
DRG-10599: 列没有编制索引
已用时间:  00: 00: 06.02
Ora92@test>create index in_text on aab(c) indextype is ctxsys.context;索引已创建。已用时间:  00: 00: 07.07
Ora92@test>select contains(c,'0') from aab;CONTAINS(C,'0')
---------------
              0
              0
              0已用时间:  00: 00: 00.04
Ora92@test>

Answer 5

我的索引已经建立了，现在的问题是我既希望获得contains的高效率（like太慢），又希望结果正确。感觉上contains只能识别单个的词，当要检索的词在目标列中是单个的词时可以。另外，contains好像对中文的检索结果不准。

Answer 6

waterfirer(水清) ，你建索引了吗？我的感觉是contains和like相比，检索出的结果可能要少，但是应该不会多。

Answer 7

不会少呀。where contains(field, value)>0等同于where field like '%value%'

Answer 8

今天的结果，和昨天又不一样，奇怪？
SQL> select userid,contains(userid,'first') from a;USERID               CONTAINS(USERID,'FIRST')
-------------------- ------------------------
the first one                               5
the first                                   5
userid                                      0
userid1                                     0
a                                           0
a                                           0
first天安门                                 0
first 天安门                                5
the first 天安门                            5
a                                           0
userid12                                    0USERID               CONTAINS(USERID,'FIRST')
-------------------- ------------------------
userid2                                     0
userid2                                     0
userid                                      0已选择14行。SQL>