|Oracle Text Application Developer's Guide
Part Number A90122-01
Query Tuning, 5 of 5
Issuing a query with more than one predicate can cause a blocking operation in the execution plan. For example, consider the following mixed query:
select docid from myindex where contains(text, 'oracle', 1) > 0 AND colA > 5 AND colB > 1 AND colC > 3;
Assume that all predicates are unselective and colA, colB, and colC have bitmap indexes. The Oracle cost-based optimizer chooses the following execution plan:
TABLE ACCESS BY ROWIDS BITMAP CONVERSION TO ROWIDS BITMAP AND BITMAP INDEX COLA_BMX BITMAP INDEX COLB_BMX BITMAP INDEX COLC_BMX BITMAP CONVERSION FROM ROWIDS SORT ORDER BY DOMAIN INDEX MYINDEX
Since the BITMAP AND is a blocking operation, Oracle must temporarily save the rowid and score pairs returned from the Oracle Text domain index before executing the BITMAP AND operation.
Oracle attempts to save these rowid and score pairs in memory. However, when the size of the result set containing these rowid and score pairs exceeds the SORT_AREA_SIZE initialization parameter, Oracle spills these results to temporary segments on disk.
Since saving results to disk causes extra overhead, you can improve performance by increasing the SORT_AREA_SIZE parameter using ALTER SESSION as follows:
For example, to set the buffer to approximately 8 megabytes, you can issue: