I have recently learned about Oracle Text and have implemented it to help a developer with his wildcard queries.
It's working very well and the queries are super fast.
However, it seems there are failures for some of the queries. The developer can see the errors in the apache logs. One user was helpful enough to call and tell us exactly what they were searching for and now we can produce the error ourselves.
Here is the full error:
select parid, owner, addrfull from schema.search_rp where contains(TEXT_MULTI_COL_IDX, '%2 MYSTREET%') > 0 offset 0 rows fetch
next 25 rows only
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
I see that one solution is to increase the "WILDCARD_MAXTERMS" from the default which is 20000.
Per an Oracle document, I increased it to 50000 which was bad and caused all our server resources to max out. So I put it back to 20000.
So I'm thinking that perhaps I need to see if I should modify the Printjoin to identify more special characters. Perhaps the data has some characters that is causing it to split the data too much.
Here is my printjoin which should not split on @\&.-,()#"_'
I'm not confident it's working properly and not confident that I've identified all the non-alphabetic and non-numeric values.
Perhaps this is not even my issue and it's simply the spaces in the data (which I need).
Can someone assist me in 2 ways:
1. Query all columns of the table for anything that is not a space, Alpha or Numeric character?
2. Make sure my printjoin is correct to account for all those special characters?
I realize it's possible there may be "unprintable" characters in the data that may need to be fixed. Maybe a user pasted something into a field. If I could identify those also, it would be great!