This post has nothing to do with Oracle Objects. Perhaps some moderator will move it to the Oracle Text sub-forum/space, where it belongs.
Your search for 'CL Mast*' did not find any rows because there aren't any rows that match that criteria. If you want to return rows that have that string, then you need to add a leading wildcard. Technically CTXCAT indexes and CATSEARCH do not support leading wildcards, but you can use two asterisks as a workaround, so you can search for '**CL Mast*'.
Your wildcard_maxterms is set to 15000, so if there are more than 15000 words that begin with 's' in your 3 million records, then it will result in an error. If you upgrade to Oracle 11g, then you can set the wildcard_maxterms higher or to unlimited by setting it to 0, but that may cause your system to run out of memory. Most applications trap these errors and return a simple message to the user, indicating that the search for s* is too broad and to narrow the search.
I would use a CONTEXT index with CONTAINS instead of CTXCAT and CATSEARCH. It supports leading wildcards and you can index substrings for faster searches. If you want it to be like the CTXCAT index then you can make it transactional.
Thanks for the reply Barbara.
I was not paying attention to where this post should go. I will find out "Oracle Text" forum and try to post the question there, if I have any.
I tried with 2 leading wildcards. I saw this suggestion from you in previous dicussions. Using 2 leading wild cards, slowed down the query on 3 million rows. Almost took 28 sec to return result set of 7 rows.
I tried using CONTEXT index and CONTAINS search, and got the same error. DRG-51030: wildcard query expansion resulted in too many terms
May be I have to use proper wild cards. I will check the documentation and try one more time. Will post the details again.