No records found for string containing 'TT'

peabody3

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?

Roger Ford-Oracle

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

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

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.