Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Text search on ".com" extremely slow

585870Jun 30 2007 — edited Jul 2 2007
Is 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 30 2007
Added on Jun 30 2007
1 comment
1,382 views