1 Reply Latest reply: Sep 3, 2013 1:10 PM by Barbara Boehmer RSS

    Text index CTXSYS questions

    J.Roosendaal

      Hi All,

       

      I have a table with 15 fields and i want to search in 2 of them.

       

      Already have the following index created:

       

      BEGIN

        CTX_DDL.CREATE_PREFERENCE ('varia', 'MULTI_COLUMN_DATASTORE');

        CTX_DDL.SET_ATTRIBUTE ('varia', 'COLUMNS', pronr, eance');

      END;

       

        CREATE INDEX indexname

            ON varia (pronr)

            INDEXTYPE IS CTXSYS.CONTEXT

            PARAMETERS ('DATASTORE varia');

       

      I need to search with:

       

      SELECT *

         FROM varia

         WHERE CATSEARCH (pronr, '%777%',1)>0;

       

      Is this correct, or is there an other way to search in both field like an 'like statement'?

        • 1. Re: Text index CTXSYS questions
          Barbara Boehmer

          You need to use CONTAINS, not CATSEARCH, with a CONTEXT index, as demonstrated below.  You may also want to increase wildcard_maxterms and add substring indexing.

           

          SCOTT@orcl12c> CREATE TABLE varia

            2    (pronr  VARCHAR2 (30),

            3      eance  VARCHAR2 (30))

            4  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT ALL

            2  INTO varia VALUES ('A777B', 'WHATEVER')

            3  INTO varia VALUES ('WHATEVER', 'B777C')

            4  INTO varia VALUES ('C777D', 'D777E')

            5  INTO varia VALUES ('WHATEVER', 'WHATEVER')

            6  SELECT * FROM DUAL

            7  /

           

          4 rows created.

           

          SCOTT@orcl12c> BEGIN

            2    CTX_DDL.CREATE_PREFERENCE ('varia', 'MULTI_COLUMN_DATASTORE');

            3    CTX_DDL.SET_ATTRIBUTE ('varia', 'COLUMNS', 'pronr, eance');

            4  END;

            5  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> CREATE INDEX indexname

            2  ON varia (pronr)

            3  INDEXTYPE IS CTXSYS.CONTEXT

            4  PARAMETERS ('DATASTORE varia')

            5  /

           

          Index created.

           

          SCOTT@orcl12c> SELECT *

            2  FROM   varia

            3  WHERE  CONTAINS (pronr, '%777%', 1) > 0

            4  /

           

          PRONR                          EANCE

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

          A777B                          WHATEVER

          WHATEVER                       B777C

          C777D                          D777E

           

          3 rows selected.