Forum Stats

  • 3,815,636 Users
  • 2,259,064 Discussions
  • 7,893,194 Comments

Discussions

No records found for string containing 'TT'

peabody3
peabody3 Member Posts: 8
edited Jun 18, 2019 3:41PM in Text

I'm running 11.2.0.4 and encountered an interesting problem with Oracle text search.  I have a field containing "TT" which is an Oracle reserve word, but delimiting it doesn't help:

SQL> SELECT program_title, count(*) FROM epg_title_search WHERE program_title LIKE 'Craig of%' GROUP BY program_title;

PROGRAM_TITLE               COUNT(*)

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

Craig of TT Creek                 68

SQL> SELECT program_title, count(*) FROM epg_title_search WHERE contains (program_title, '%Craig of {TT} Creek%') > 0 GROUP BY program_title;

no rows selected

Even if I don't reference the "TT" nothing is found:

SQL> SELECT program_title, count(*) FROM epg_title_search WHERE contains (program_title, '%Craig of%') > 0 GROUP BY program_title;

no rows selected

SQL> SELECT program_title, count(*) FROM epg_title_search WHERE contains (program_title, '%Craig%') > 0 GROUP BY program_title;

PROGRAM_TITLE               COUNT(*)

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

Craigslist Joe                     4

Sanjay and Craig                 116

Craig Cameron                     12

Delimiting other reserved words works find:

SQL> SELECT program_title, COUNT (*) FROM epg_title_search WHERE contains (program_title, '%The Cat in the Hat Knows a Lot {About} That%') > 0 GROUP BY program_title;

PROGRAM_TITLE                                COUNT(*)

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

The Cat in the Hat Knows a Lot About That!       1583

Anyone else encounter this?  Is there a work around?

Answers

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jun 14, 2019 12:36PM

    I'm not seeing that if I test it.

    I can explain why '%Craig of%' doesn't find anything.

    'of' is a default stopword, so is not indexed.

    However, you're searching for 'of%', which means it's looking for words beginning with 'of' - perhaps 'often', 'official', 'offal', etc. In this case it will NOT treat the 'of' part of it as a wildcard.

    Here's the full test which works for me:

    SQL> create table test (text varchar2(2000));

    Table created.

    SQL> insert into test values ('Craig of TT Creek');

    1 row created.

    SQL> create index testindex on test(text) indextype is ctxsys.context;

    Index created.

    SQL> select * from test where contains(text, '%Craig of {TT} Creek%') > 0;

    TEXT

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

    Craig of TT Creek

    As an aside, it's not a great idea to just put wildcards on either side of your search string.  It doesn't achieve anything here - it's not needed and the full words are present - and it can have a serious effect on performance because the leading % on Craig will prevent it using the index on the $I table to look up tokens ending with 'CRAIG'.

    Instead you should just be doing

    ... WHERE contains (program_title, 'Craig of {TT} Creek') > 0

  • peabody3
    peabody3 Member Posts: 8
    edited Jun 18, 2019 3:13PM

    Thanks Roger.  I repeated what you did with the test table and it worked fine, and then it occurred to me that I needed to rebuild the indexes after updating the table.  Once I did that, the query worked fine.  Also good point about the wildcards - that was just for testing but don't exist in the production code.

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jun 18, 2019 3:41PM

    OK, great. Of course you don't need to do a complete rebuild after updating the table, you can just sync updates by calling ctx_ddl.sync_index('<yourindexname>').

    Alternatively you can create the index with a clause such as PARAMETERS ('SYNC (ON COMMIT)')  or PARAMETERS ('SYNC (EVERY "freq=minutely;interval=1")') either of which will cause it to sync automatically.

    You probably knew that, but it may help anyone else who stumbles across this answer.