Oracle Text Contains Problem

Hi Everyone,
I'm using the Oracle Text contains function, but it sometimes doesn't work properly.  It returns 0 (text not found) when the text is actually there.

For example:

select e.* , contains(e.emp_name, '%Jones%') as cont_val
from emp_table e
where e.emp_name = 'Jones'

Returns a row with cont_val = 0 (which means text not found).  Results for %Jones% in the contains function in the where clause return no rows.  This is a sporadic problem that only occurs for some rows in our table.  We're using Oracle 10.2.0.1.0.
Any ideas what's going on?
Thanks

LVL 1
leclaudeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mrjoltcolaConnect With a Mentor Commented:
That is how text indexes work. Text indexes are not standard Oracle b-tree indexes. The update does not happen immediately upon insert or update of the rows. Instead the change is scheduled, and will be applied when the next sync is run. In 10g and 11g I believe there is a setting that forces immediate update but it is poor for performance.

Please read this for an explanation:

http://www.oracle.com/technology/products/text/htdocs/Index_Maintenance.html
0
 
mrjoltcolaCommented:
How are you updating your text indexes? They are not immediately synched and must be maintained, unlike regular indexes.

http://download.oracle.com/docs/cd/B15595_01/content.101/b14493/text.htm#sthref776

See the docs on ctx_ddl.sync_index() for manually synching, try a manual sync and see if it shows.

0
 
leclaudeAuthor Commented:
Why would an index need to be synched to return a row? At worst the query should be slow, but not miss results.
0
All Courses

From novice to tech pro — start learning today.