1 Reply Latest reply on Jun 12, 2014 2:21 PM by Barbara Boehmer

    Oracle Text index catsearch order by close match

    2b3d6127-fc13-452c-b757-d9f46e72fa5f

      I am using oracle text index of type ctxcat, but I am new oracle text. I am able to query the table and getting the right results. But we want the results in below order.

      If the search string contains two strings then order of the results should be

      In top position: Results that have the exact match to the input text

      Then: results that have both words

      And at the end: results that have at least one of the two words.

        • 1. Re: Oracle Text index catsearch order by close match
          Barbara Boehmer

          I have provided one method using a CTXCAT index below, followed by another method using a CONTEXT index.

           

          SCOTT@orcl12c> CREATE TABLE test_tab

            2    (test_col  VARCHAR2(60))

            3  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT ALL

            2  INTO test_tab VALUES ('word1 word3 word2')

            3  INTO test_tab VALUES ('word3 word1 word2')

            4  INTO test_tab VALUES ('word1 word2 word3')

            5  INTO test_tab VALUES ('word1 word3')

            6  INTO test_tab VALUES ('word2 word3')

            7  INTO test_tab VALUES ('word1 word2')

            8  INTO test_tab VALUES ('word1')

            9  INTO test_tab VALUES ('word2')

          10  INTO test_tab VALUES ('other words')

          11  SELECT * FROM DUAL

          12  /

           

          9 rows created.

           

          SCOTT@orcl12c> CREATE INDEX test_col_ctxcat

            2  ON test_tab (test_col)

            3  INDEXTYPE IS CTXSYS.CTXCAT

            4  /

           

          Index created.

           

          SCOTT@orcl12c> SELECT test_col

            2  FROM   (SELECT test_col FROM test_tab

            3           WHERE  CATSEARCH

            4                (test_col,

            5                 '<query>

            6                <textquery lang="ENGLISH" grammar="CONTEXT">

            7                  <progression>

            8                    <seq>word1 word2</seq>

            9                    <seq>word1 AND word2</seq>

          10                    <seq>word1 OR word2</seq>

          11                  </progression>

          12                </textquery>

          13                <score datatype="INTEGER" algorithm="COUNT"/>

          14              </query>',

          15              null) > 0)

          16  ORDER  BY DECODE (test_col, 'word1 word2', 0, ROWNUM)

          17  /

           

          TEST_COL

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

          word1 word2

          word3 word1 word2

          word1 word2 word3

          word1 word3 word2

          word1 word3

          word2 word3

          word1

          word2

           

          8 rows selected.

           

          SCOTT@orcl12c> CREATE INDEX test_col_context

            2  ON test_tab (test_col)

            3  INDEXTYPE IS CTXSYS.CONTEXT

            4  /

           

          Index created.

           

          SCOTT@orcl12c> SELECT test_col FROM test_tab

            2  WHERE  CONTAINS

            3           (test_col,

            4            '(word1 word2 * 10 * 10) OR

            5             (word1 AND word2) OR

            6             (word1 OR word2) * 0.1 * 0.1',

            7            1) > 0

            8  ORDER  BY SCORE(1) DESC, UTL_MATCH.EDIT_DISTANCE (test_col, 'word1 word2')

            9  /

           

          TEST_COL

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

          word1 word2

          word3 word1 word2

          word1 word2 word3

          word1 word3 word2

          word1 word3

          word2 word3

          word2

          word1

           

          8 rows selected.