1 Reply Latest reply: Aug 4, 2010 9:30 AM by Dom Brooks RSS

    Oracle Text contains clause

    737117
      Hi everybody,

      I'm having a little problem with a query with a contains clause for oracle text. example:

      1) i have a table with names:

      Name
      --------
      A EN B WORKS
      A EN C SERVICES
      A EN DC SERVICES
      A EN EC SERVICES

      2) I have an ctxsys.context index on this column:

      CREATE INDEX NAME_IDX ON COMPANIES (NAME) INDEXTYPE IS CTXSYS.CONTEXT;

      3)I want to search "A EN C SERVICES" with "contains" in the where clause:

      select * from companies where contains (name, 'A EN C SERVICES') > 0;

      Now this query gives me the following result:

      A EN B WORKS
      A EN C SERVICES
      A EN DC SERVICES
      A EN EC SERVICES

      how is this possible? the first value doesn't even contain a 'C'.
      How do i do an exact search for 'A EN C SERVICES'?

      Greets,
      Mario
        • 1. Re: Oracle Text contains clause
          Dom Brooks
          I suggest you read up a little more on what a CONTEXT index is.
          http://download.oracle.com/docs/cd/E11882_01/text.112/e10945/overview.htm#CCAPP9001

          If this is not what you want then either a) you want to update your score threshold from > 0 or b) perhaps you don't want a context index.

          In general, a CONTEXT index isn't really ideally suited to searching a single name for a term because of the scoring algorithm