If you issue a query with more than one predicate, you can cause a blocking operation in the execution plan. For example, consider the following mixed query:

select docid from mytab 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 CBO in Oracle Database 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

Because BITMAP AND is a blocking operation, Oracle Text must temporarily save the rowid and score pairs returned from the Oracle Text domain index before it runs the BITMAP AND operation.

Oracle Text attempts to save these rowid and score pairs in memory. However, when the size of the result set exceeds the SORT_AREA_SIZE initialization parameter, Oracle Text spills these results to temporary segments on disk.

Because saving results to disk causes extra overhead, you can improve performance by increasing the SORT_AREA_SIZE parameter.

alter session set SORT_AREA_SIZE = <new memory size in bytes>;

For example, set the buffer to approximately 8 megabytes.

alter session set SORT_AREA_SIZE = 8300000;