1 2 3 Previous Next 35 Replies Latest reply: Jan 9, 2012 3:10 PM by 876250 Go to original post RSS
      • 30. Re: Achieving functionality of many preferences using one context index
        876250
        Hi Gurus,

        This post is a follow up of post Re: Achieving functionality of many preferences using one context index

        I have two issues here. I am splitting into two parts

        Partial Search not working_

        I am inserting a record
        SQL> insert into test_sh values(31,'Oracle Text enables you to build text query applications and document classification
        applications. Oracle Text provides indexing, word and theme searching, and viewing
        capabilities for text');

        1 row created.

        SQL> commit;

        Commit complete.

        SQL> exec ctx_ddl.sync_index('ix_test_sh');

        PL/SQL procedure successfully completed.

        Following query doesn't return any results
        SQL> SELECT * FROM TEST_SH where contains (text, format_search ('APPL')) > 0;

        no rows selected
        Following query returns results
        SQL> SELECT * FROM TEST_SH where contains (text, format_search ('APPLICATION')) > 0;
        TEXT_ID
        ----------
        TEXT
        --------------------------------------------------------------------------------
        31
        Oracle Text enables you to build text query applications and document classifica

        If I give APPL or APPLICATION I thought by default Oracle returns the same value.
        SQL> select * from test_sh where TEXT like '%MA%'
        2 /

        TEXT_ID TEXT
        ---------- ------------------------------
        20 MA
        9 MARK ANTONY
        10 MARK
        10 MARKANTONY
        SQL> SELECT * FROM TEST_SH where contains (text, format_search ('MA')) > 0;

        TEXT_ID TEXT
        ---------- ------------------------------
        20 MA
        insert into test_sh values(7,'KITCHEN');
        If I query for 'KITCH' or 'KITCHEN' it returns the same value. So this is working as expected.
        SQL> SELECT * FROM TEST_SH where contains (text, format_search ('KITCH')) > 0 ;

        TEXT_ID TEXT
        ---------- ------------------------------
        7 kitchen
        I was assuming partial search is a default behavior in Oracle Text Search .

        Search not working as intended*

        SQL> Insert into TEST_SH values (30,'The Griddler is an amazing contact grill, open grill, griddle and panini press - all in one!With removable, dishwasher-safe non-stick grill and griddle plates, drip cups and scraping tool, plus a floating hinge and dual temperature controls, it expertly cooks everything from pancakes to sausage to grilled cheese, steaks, hamburgers and panini - and cleans up effortlessly. *Warranted Product.');

        1 row created.

        SQL> commit;

        Commit complete.

        SQL> exec ctx_ddl.sync_index('ix_test_sh');

        PL/SQL procedure successfully completed.

        SQL> SELECT * FROM TEST_SH where contains (text, format_search ('STAPLES')) > 0;

        TEXT_ID TEXT
        ---------- ------------------------------
        30 The Griddler is an amazing con
        tact grill, open grill, griddl
        e and panini press -
        here we are searching for STAPLES and the record which is returned doesn't have STAPLES itself.

        Please let me know where I am going wrong.
        • 31. Re: Achieving functionality of many preferences using one context index
          Roger Ford-Oracle
          No, partial (word) search is not default behavior in Oracle Text, you have to use wild cards if you want to to do a partial word search.

          You're using the "format_search" function in your query. Is that exactly as Barbara posted?

          What do you get if you do:
          select partial_search('STAPLES') from dual;
          Presumably one of the expansions generated by the partial_search function is matching something in your text.
          • 32. Re: Achieving functionality of many preferences using one context index
            876250
            I am using the same function which Barabara posted.
            SQL> select format_search('STAPLES') from dual;

            FORMAT_SEARCH('STAPLES')
            --------------------------------------------------------------------------------
            (syn (STAPLES) or ?STAPLES or !STAPLES or $STAPLES)
            insert into test_sh values(7,'KITCHEN');
            If I query for 'KITCH' or 'KITCHEN' it returns the same value. So this is working as expected.
            SQL> SELECT * FROM TEST_SH where contains (text, format_search ('KITCH')) > 0 ;
            TEXT_ID TEXT
            ------------------------------
            7 kitchen
            I was thinking here when we search for KITCH it was working. I thought hence partial search will work.
            • 33. Re: Achieving functionality of many preferences using one context index
              Barbara Boehmer
              The function converts the search string to check for synonym or fuzzy or soundex or stem. The word "steaks" in your data is both a fuzzy match and soundex match for the word "staples". However, it is a very low score of only 4, so you could eliminate such things from your results by selecting only those rows above a certain score or just order by score descending to list them last. I have provided a partial demonstration below, where text_id 30 is your provided data, text_id 31 is the same data without the word "steaks" and text_id 32 has only the word steaks. I have then demonstrated what the function returns and what the individual parts of the function return. Fuzzy and soundex both find 30 and 32, but not 31.
              SCOTT@orcl_11gR2> insert into TEST_SH values (30,
                2  'The Griddler is an amazing contact grill, open grill, griddle and panini press -
                3  all in one!With removable, dishwasher-safe non-stick grill and griddle plates,
                4  drip cups and scraping tool, plus a floating hinge and dual temperature controls,
                5  it expertly cooks everything from pancakes to sausage to grilled cheese, steaks,
                6  hamburgers and panini - and cleans up effortlessly. *Warranted Product.')
                7  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> insert into TEST_SH values (31,
                2  'The Griddler is an amazing contact grill, open grill, griddle and panini press -
                3  all in one!With removable, dishwasher-safe non-stick grill and griddle plates,
                4  drip cups and scraping tool, plus a floating hinge and dual temperature controls,
                5  it expertly cooks everything from pancakes to sausage to grilled cheese,
                6  hamburgers and panini - and cleans up effortlessly. *Warranted Product.')
                7  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> insert into TEST_SH values (32, 'steaks')
                2  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> create index ix_test_sh on test_sh (text)
                2  indextype is ctxsys.context
                3  /
              
              Index created.
              
              SCOTT@orcl_11gR2> select format_search ('STAPLES') from dual
                2  /
              
              FORMAT_SEARCH('STAPLES')
              --------------------------------------------------------------------------------
              (syn (STAPLES) or ?STAPLES or !STAPLES or $STAPLES)
              
              1 row selected.
              
              SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                2  where  contains (text, format_search ('STAPLES'), 1) > 0
                3  /
              
                SCORE(1)    TEXT_ID
              ---------- ----------
              TEXT
              --------------------------------------------------------------------------------
                       4         30
              The Griddler is an amazing contact grill, open grill, griddle and panini press -
              
              all in one!With removable, dishwasher-safe non-stick grill and griddle plates,
              drip cups and scraping tool, plus a floating hinge and dual temperature controls
              ,
              it expertly cooks everything from pancakes to sausage to grilled cheese, steaks,
              
              hamburgers and panini - and cleans up effortlessly. *Warranted Product.
              
                       4         32
              steaks
              
              
              2 rows selected.
              
              SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                2  where  contains (text, 'syn (STAPLES)', 1) > 0
                3  /
              
              no rows selected
              
              SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                2  where  contains (text, '?STAPLES', 1) > 0
                3  /
              
                SCORE(1)    TEXT_ID
              ---------- ----------
              TEXT
              --------------------------------------------------------------------------------
                       4         30
              The Griddler is an amazing contact grill, open grill, griddle and panini press -
              
              all in one!With removable, dishwasher-safe non-stick grill and griddle plates,
              drip cups and scraping tool, plus a floating hinge and dual temperature controls
              ,
              it expertly cooks everything from pancakes to sausage to grilled cheese, steaks,
              
              hamburgers and panini - and cleans up effortlessly. *Warranted Product.
              
                       4         32
              steaks
              
              
              2 rows selected.
              
              SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                2  where  contains (text, '!STAPLES', 1) > 0
                3  /
              
                SCORE(1)    TEXT_ID
              ---------- ----------
              TEXT
              --------------------------------------------------------------------------------
                       4         30
              The Griddler is an amazing contact grill, open grill, griddle and panini press -
              
              all in one!With removable, dishwasher-safe non-stick grill and griddle plates,
              drip cups and scraping tool, plus a floating hinge and dual temperature controls
              ,
              it expertly cooks everything from pancakes to sausage to grilled cheese, steaks,
              
              hamburgers and panini - and cleans up effortlessly. *Warranted Product.
              
                       4         32
              steaks
              
              
              2 rows selected.
              
              SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                2  where  contains (text, '$STAPLES', 1) > 0
                3  /
              
              no rows selected
              • 34. Re: Achieving functionality of many preferences using one context index
                Barbara Boehmer
                The situation is similar for KITCH and kitchen, as they are a fuzzy match, with a score of 3, as demonstrated below. So, you are getting results due to the fuzzy match, not due to some non-existent default partial search.
                SCOTT@orcl_11gR2> insert into TEST_SH values (7, 'kitchen')
                  2  /
                
                1 row created.
                
                SCOTT@orcl_11gR2> create index ix_test_sh on test_sh (text)
                  2  indextype is ctxsys.context
                  3  /
                
                Index created.
                
                SCOTT@orcl_11gR2> select format_search ('KITCH') from dual
                  2  /
                
                FORMAT_SEARCH('KITCH')
                --------------------------------------------------------------------------------
                (syn (KITCH) or ?KITCH or !KITCH or $KITCH)
                
                1 row selected.
                
                SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                  2  where  contains (text, format_search ('KITCH'), 1) > 0
                  3  /
                
                  SCORE(1)    TEXT_ID
                ---------- ----------
                TEXT
                --------------------------------------------------------------------------------
                         3          7
                kitchen
                
                
                1 row selected.
                
                SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                  2  where  contains (text, 'syn (KITCH)', 1) > 0
                  3  /
                
                no rows selected
                
                SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                  2  where  contains (text, '?KITCH', 1) > 0
                  3  /
                
                  SCORE(1)    TEXT_ID
                ---------- ----------
                TEXT
                --------------------------------------------------------------------------------
                         3          7
                kitchen
                
                
                1 row selected.
                
                SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                  2  where  contains (text, '!KITCH', 1) > 0
                  3  /
                
                no rows selected
                
                SCOTT@orcl_11gR2> SELECT score (1), text_id, text FROM TEST_SH
                  2  where  contains (text, '$KITCH', 1) > 0
                  3  /
                
                no rows selected
                • 35. Re: Achieving functionality of many preferences using one context index
                  876250
                  Thanks for the detailed explanation. Now I got the reason behind the issues which we were facing......
                  1 2 3 Previous Next