Situation Overview

In a test environment using Oracle Text Search (OTS), we started seeing timeouts and sort errors right after a DB copy. When OTS was disabled and DB-path search was used instead, certain queries failed to return results because the sort directive (SortSpec) was rejected. Interestingly, with the same conditions the administrator account (e.g., weblogic) worked fine while general users failed. This called for deeper analysis.

Symptoms & Logs (with inline notes)

1) SortSpec rejected under DB-path

Caused By: intradoc.data.DataException: !csSearchInvalidOrderByClause,dDocTitle ASC
# Meaning: DB-path determined the SortSpec was NOT a “complete ORDER BY clause” and rejected it.

Caused By: intradoc.data.DataException: !csSearchInvalidOrderByClause,xPublishDate DESC\, sddDocTitle ASC
# Meaning: validation failed due to a backslash before the comma and/or use of a UI alias instead of a real column name.

2) With OTS enabled: timeouts and wildcard expansion threshold

ORA-01013: user requested cancel of current operation
# Meaning: server-side cancel (timeout/user cancel/resource limit) interrupted the execution.

DRG-51030: wildcard query expansion resulted in too many terms
# Meaning: wildcard expansion (turning patterns into term lists) exceeded the configured limit.

Diagnosis

The problem manifested differently in two paths. Under DB-path, server-side validation for ordering was active: if SortSpec was not a complete ORDER BY clause (e.g., order by col1 DESC, col2 ASC), or if it contained UI aliases/backslashes, the search service refused to return results. Under OTS, right after the DB copy there was a chance of a transient mismatch between the OTS collection and DB metadata, and for general users specifically, the combination of security filters with wildcards likely caused a surge in wildcard expansion terms.

Action ①: Fix SortSpec under DB-path

We first modified the failing scripts on the DB-path route and re-ran with identical conditions. The key was to use a full ORDER BY clause, reference real column names (not UI aliases), and remove backslashes. After the changes, tests completed successfully.

@Properties LocalData
IdcService=GET_SEARCH_RESULTS
UserTimeZone=UTC
QueryText=... (omitted)
SortSpec=dDocTitle ASC               # ← not a complete ORDER BY clause
ResultCount=10
StartRow=1
@end

@Properties LocalData
IdcService=GET_SEARCH_RESULTS
UserTimeZone=UTC
QueryText=... (omitted)
SortSpec=order by xPublishDate DESC, dDocTitle ASC  # full ORDER BY + comma
ResultCount=10
StartRow=1
@end

Action ②: Reconfigure OTS after DB copy

Immediately after a DB copy, OTS collections and DB objects can be slightly out of sync. We enabled OTS, ran a Collection Rebuild once, applied WORDLIST attributes, and then performed an online index rebuild.

1) Run Collection Rebuild once

In the WCC Admin console, go to Administration → Repository Manager → Indexer (or “Search Index/Collections” depending on the version), and click Rebuild Entire Index. Before running, confirm SearchIndexerEngineName=OracleTextSearch is enabled (also check Additional Configuration in config.cfg if needed). Prefer a low-traffic window.

2) Apply WORDLIST attributes (renamed)

We use DOC_WORDLIST as the WORDLIST name. Increase the wildcard expansion limit stepwise (e.g., 25,000 → 50,000) by agreement.

BEGIN
  CTX_DDL.SET_ATTRIBUTE('DOC_WORDLIST', 'WILDCARD_MAXTERMS', '25000'); 
  -- Meaning: set wildcard expansion term limit to 25,000 (raise gradually as needed)
END;
/

3) Online rebuild related indexes (renamed)

To avoid exposing actual customer object names, we refer to the indexes as IDX_DOCTEXT_A and IDX_DOCTEXT_B (schema example: APP_SCHEMA).

ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_A 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_B 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

4) Verify application

SELECT CTX_REPORT.DESCRIBE_INDEX('APP_SCHEMA.IDX_DOCTEXT_A') FROM dual;
SELECT CTX_REPORT.DESCRIBE_INDEX('APP_SCHEMA.IDX_DOCTEXT_B') FROM dual;
-- Meaning: confirm WORDLIST/LEXER and other attributes are actually applied.

Action ③: Admin is fine, general users fail or are slow

In OTS mode, if the admin account works but general users are slow or fail, suspect that security filters (ACL/Account/Group) combined with the query are inflating wildcard expansion. Improve in this order:

1. Apply SDATA optimization to security columns
   In Configuration Manager → Search → Fields, set Is Optimized (SDATA) for the security field (e.g., dDocAccount) to lighten condition evaluation when security filters are combined.
2. Review wildcard usage
   Limit heavy patterns like *term* for general users. If necessary, raise DOC_WORDLIST.WILDCARD_MAXTERMS stepwise (e.g., 25,000 → 35,000 → 50,000) while monitoring load.
3. Check timeout/resource parameters
   Review application/DB timeouts and resource caps (e.g., JDBC timeouts, session limits) so ORA-01013 does not fire prematurely.

Validation & Production Rollout

We first corrected SortSpec on the DB-path route and confirmed the service completed successfully. We then applied the same ordering format to the WCC UI configuration and validated end-to-end search there as well, followed by rolling the configuration out to production.

For the OTS route, we completed pre-validation in test (Collection Rebuild plus WORDLIST rebind and online index rebuild). To further improve stability and performance for general users, we scheduled additional scenarios to test SDATA optimization of security fields and wildcard parameter tuning. We will publish the results in a follow-up post.

Checklist

• Under DB-path, always use a SortSpec=order by ... complete clause (with commas, no aliases/backslashes).
• Right after a DB copy, clean up OTS in this order: Collection Rebuild → apply WORDLIST → online index rebuild.
• Tune wildcard expansion with DOC_WORDLIST.WILDCARD_MAXTERMS (stepwise, up to 50,000 if agreed).
• If only general users are slow/failing, prioritize SDATA optimization on the security column(s).
• Before release, re-verify the UI search end-to-end with the same settings.

Appendix: Useful Snippets

-- Apply WORDLIST attribute (example: 25,000)
BEGIN
  CTX_DDL.SET_ATTRIBUTE('DOC_WORDLIST', 'WILDCARD_MAXTERMS', '25000');
END;
/


-- Rebuild indexes ONLINE with WORDLIST rebind
ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_A 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_B 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

-- Inspect index attributes
SELECT CTX_REPORT.DESCRIBE_INDEX('APP_SCHEMA.IDX_DOCTEXT_A') FROM dual;
SELECT CTX_REPORT.DESCRIBE_INDEX('APP_SCHEMA.IDX_DOCTEXT_B') FROM dual;

@Properties LocalData
IdcService=GET_SEARCH_RESULTS
UserTimeZone=UTC
QueryText=(... omitted ...) <AND> dDocTitle <substring> <qsch>test</qsch>
SortSpec=order by xPublishDate DESC, dDocTitle ASC
ResultCount=10
StartRow=1
@end