Parallel Queries on a Local Context Index

Parallel query refers to the parallelized processing of a local CONTEXT index.

Based on the parallel degree of the index and various system attributes, Oracle determines the number of parallel query workers to be spawned to process the index. Each parallel query worker processes one or more index partitions. This default query behavior applies to local indexes that are created in parallel.

However, for heavily loaded systems with a high number of concurrent users, query throughput is usually not effective with parallel query; if the query is run serially, the top-N hits can usually be satisfied by the first few partitions. For example, take the typical top-N text queries with an ORDER BY partition key column:

select * from (
        select story_id from stories_tab where contains(...)>0 order by
publication_date desc)
    where rownum <= 10;

These text queries generally do not perform well with a parallel query.

You can disable parallel querying after a parallel index operation with an ALTER INDEX statement:

Alter index <text index name> NOPARALLEL;
Alter index <text index name> PARALLEL 1;

You can also enable or increase the parallel degree:

Alter index <text index name> parallel < parallel degree >;