Text search on ".com" extremely slow
585870Jun 30 2007 — edited Jul 2 2007Is there an oracle reason why doing a text search on the string ".com" would be extremely slow?
Background:
We are using indexed text search in a contact management system. In order to index complete email addresses we have defined the following characters as printjoins characters: period (.), aspersand (@), apostrophe ('), underscore (_), and hyphen (-).
Problem:
Executing this sql, searching ".com" in the cem_email field takes ~187 secs to return results:
SELECT count(*) FROM contact,con_email
WHERE (con_id = cem_con_id)
AND (con_rec_status = 'A'
AND contains(con_text, ' ( ( %.com% ) WITHIN cem_email) ') > 0)
GROUP BY con_id, con_last_name, con_first_name
Doing the same sql in any other column, ie those I wouldn't expect to have email addresses, also takes a long time. This one returns in ~147 secs:
SELECT count(*) FROM contact
WHERE (con_rec_status = 'A'
AND contains(con_text, ' ( ( %.com% ) WITHIN con_last_name OR ( %.com% ) WITHIN con_alt_last_name) ') > 0)
GROUP BY con_id, con_last_name, con_first_name
Removing the period (.) => search %com% in both of the above sql's results in oci error:
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms
Interestingly, when I search on other email extensions (.org/org, .net/net, or .edu/edu) search results are returned and quickly, less than 1 second.
So why does oracle have problems with com & .com?
Right now we just have to check and disallow this search.
Thank you for any insight you all can provide.
Chris