2 Replies Latest reply: Mar 9, 2013 5:35 PM by peabody3 RSS

    Odd results searching by certain letters

    peabody3
      I'm running 11.2.0.2.0 on a linux box and I'm getting strange behavior when I search for strings beginning with a single letter. To illustrate, I created the following table and populated it with 500 random names:

      SQL> desc search_test
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      NAME_ID NUMBER(10)
      F_NAME VARCHAR2(50)
      M_NAME VARCHAR2(50)
      L_NAME VARCHAR2(50)

      I then created the following index:

      BEGIN
      ctx_ddl.create_preference('epg_name_multi', 'MULTI_COLUMN_DATASTORE');
      ctx_ddl.set_attribute('epg_name_multi', 'columns', 'f_name,m_name,l_name');
      end;
      /

      CREATE INDEX search_test_idx ON search_test (f_name)
      INDEXTYPE IS ctxsys.context
      PARAMETERS ('DATASTORE epg_name_multi');

      These queries return the expected results: rows that have the f_name or m_name or l_name beginning with the letter:

      SQL> select count(*) from search_test where contains(f_name, 'a%') > 0;

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

      SQL> select count(*) from search_test where contains(f_name, 'b%') > 0;

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

      SQL> select count(*) from search_test where contains(f_name, 'c%') > 0;

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

      However, letters 'f', 'l', 'm' and 'm' return ALL rows:

      SQL> select count(*) from search_test where contains(f_name, 'f%') > 0;

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

      SQL> select count(*) from search_test where contains(f_name, 'l%') > 0;

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

      SQL> select count(*) from search_test where contains(f_name, 'm%') > 0;

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

      SQL> select count(*) from search_test where contains(f_name, 'n%') > 0;

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

      Can someone explain why these 4 letters behave this way? The other 22 work fine.

      Thanks!
      Pete
        • 1. Re: Odd results searching by certain letters
          Roger Ford-Oracle
          So you have problems with F, L, M and N? And you have columns F_Name, L_Name and M_Name? There's a pattern there.

          The problem is that MULTI_COLUMN_DATASTORE adds the column names as xml-like tags around each column's data. Unless you specify a section group, then these tags will be indexed as plain text (and the underscore, by default, is a break character so "Name" will be a token).

          I think if you specify BASIC_SECTION_GROUP or HTML_SECTION_GROUP then the tags will not be indexed. Alternatively, you can set the attribute DELIMITER to 'NEWLINE' in the MULTI_COLUMN_DATASTORE, and it will not insert tags around the sections.
          http://oracledocs.shu.ac.uk/oracle/B28359_01/text.111/b28304/cdatadic.htm#i1006391

          Edited by: Roger Ford on Mar 9, 2013 2:30 PM
          • 2. Re: Odd results searching by certain letters
            peabody3
            Thanks Roger. I added this to the ctx_ddl definition and now it works fine:

            ctx_ddl.set_attribute('epg_name_multi', 'delimiter', 'NEWLINE');

            Never would have guessed that the column name made a difference. :)