1 Reply Latest reply: Jun 26, 2014 2:02 PM by Barbara Boehmer RSS

    CATCTX index preferences and CATSEARCH query strategies for indexing/searching dot separated word lists

    923751

      We do have a search item build from alphanumeric sub keys concatenated by dots as for example

       

      F.4E1.666.113.A

       

      Users are heavily searching with wildcards for complete sub keys or even sub key prefixes only and of course any combination of both as for example:

       

      *.4E1.666.113*

      *4E1.666.1*A

      *.4E1.*.113*

       

      Because of its transactional behavior we would like to try to improve such queries with a catctx index and the catsearch operator.

       

      Q1: What would be appropriate index build (lexer) preferences ?

      Q2: How should the catsearch query have to look like, just removing the dots and leading wildcards ?

      for instance

       

      CATSEARCH(column, '4E1 666 113*',NULL)

      CATSEARCH(column, '4E1 666 1* A',NULL)

      CATSEARCH(column, '4E1 113*',NULL)

       

      The catsearch access should at least return the same result as a simple wildcard LIKE so at best even consider the word order (alternatively we could keep the LIKE predicate as consecutive filter after the catctx access).

       

      Many thanks for any suggestion,

       

      Rainer Stenzel

        • 1. Re: CATCTX index preferences and CATSEARCH query strategies for indexing/searching dot separated word lists
          Barbara Boehmer

          SCOTT@orcl12c> -- If you have data like this:

          SCOTT@orcl12c> CREATE TABLE test_tab

            2    (id      NUMBER,

            3      test_col  VARCHAR2(30))

            4  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT ALL

            2  INTO test_tab VALUES (1, 'F 4E1 666 113 A')

            3  INTO test_tab VALUES (2, 'other data')

            4  SELECT * FROM DUAL

            5  /

           

          2 rows created.

           

          SCOTT@orcl12c> -- and you have a ctxcat index like this:

          SCOTT@orcl12c> CREATE INDEX test_idx

            2  ON test_tab (test_col)

            3  INDEXTYPE IS CTXSYS.CTXCAT

            4  /

           

          Index created.

           

          SCOTT@orcl12c> -- then any of the following searches will find the data with id=1:

          SCOTT@orcl12c> VARIABLE search_string VARCHAR2(30)

          SCOTT@orcl12c> EXEC :search_string := '4E1 666 113'

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> SELECT * FROM test_tab

            2  WHERE  CATSEARCH (test_col, :search_string, NULL) > 0

            3  /

           

                  ID TEST_COL

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

                   1 F 4E1 666 113 A

           

          1 row selected.

           

          SCOTT@orcl12c> EXEC :search_string := '4E1 666 1* A'

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> /

           

                  ID TEST_COL

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

                   1 F 4E1 666 113 A

           

          1 row selected.

           

          SCOTT@orcl12c> EXEC :search_string := '4E1  113'

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> /

           

                  ID TEST_COL

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

                   1 F 4E1 666 113 A

           

          1 row selected.