The following factors can influence query response time:

• Collection of table statistics

• Memory allocation

• Sorting

• Presence of large object (LOB) columns in your base table

• Partitioning

• Parallelism

• The number term expansions in your query

  See Also:

  "Frequently Asked Questions About Query Performance"

When you need the first rows of an ORDER BY query, Oracle recommends that you use the cost-based FIRST_ROWS(n) hint.

You use the FIRST_ROWS(n) hint in cases where you want the first n number of rows in the shortest possible time. For example, consider the following PL/SQL block that uses a cursor to retrieve the first 10 hits of a query and the FIRST_ROWS(n) hint to optimize the response time:

declare
cursor c is 

select /*+ FIRST_ROWS(10) */ article_id from articles_tab
   where contains(article, 'Omophagia')>0 order by pub_date desc;

begin

for i in c
loop
insert into t_s values(i.pk, i.col);
exit when c%rowcount > 11;
end loop;

end;
/

The c cursor is a SELECT statement that returns the rowids that contain the word omophagia in sorted order. The code loops through the cursor to extract the first 10 rows. These rows are stored in the temporary t_s table.

With the FIRST_ROWS(n) hint, the optimizer instructs the Oracle Text index to return rowids in score-sorted order when the cost of returning the top-N hits is lower.

Without the hint, Oracle Database sorts the rowids after the Oracle Text index returns all rows in unsorted order that satisfy the CONTAINS predicate. Retrieving the entire result set takes time.

Because only the first 10 hits are needed in this query, using the hint results in better performance.

You can also optimize for response time by using the related DOMAIN_INDEX_SORT hint. Like FIRST_ROWS(n), when queries are optimized for response time, Oracle Text returns the first rows in the shortest time possible.

For example, you can use this hint:

select /*+ DOMAIN_INDEX_SORT */ pk, score(1), col from ctx_tab 
            where contains(txt_col, 'test', 1) > 0 order by score(1) desc;

However, this hint is only rule-based. This means that Oracle Text always chooses the index which satisfies the ORDER BY clause. This hint might result in suboptimal performance for queries where the CONTAINS clause is very selective. In these cases, Oracle recommends that you use the FIRST_ROWS(n) hint, which is fully cost-based.

Partitioning your data and creating local partitioned indexes can improve your query performance. On a partitioned table, each partition has its own set of index tables. Effectively, there are multiple indexes, but the results are combined as necessary to produce the final result set.

Create the CONTEXT index with the LOCAL keyword:

CREATE INDEX index_name ON table_name (column_name) 
INDEXTYPE IS ctxsys.context
PARAMETERS ('...')
LOCAL

With partitioned tables and indexes, you can improve performance of the following types of queries:

• Range Search on Partition Key Column: This query restricts the search to a particular range of values on a column that is also the partition key. For example, consider a query on a date range:

  SELECT storyid FROM storytab WHERE CONTAINS(story, 'oliver')>0 and pub_date BETWEEN '1-OCT-93' AND '1-NOV-93';

  If the date range is quite restrictive, it is very likely that the query can be satisfied by only looking in a single partition.

• ORDER BY Partition Key Column: This query requires only the first n hits, and the ORDER BY clause names the partition key. Consider an ORDER BY query on a price column to fetch the first 20 hits:

  SELECT * FROM (

  SELECT itemid FROM item_tab WHERE CONTAINS(item_desc, 'cd player')
    >0 ORDER BY price)
    WHERE ROWNUM < 20;

  In this example, with the table partitioned by price, the query might only need to get hits from the first partition to satisfy the query.

The DOMAIN_INDEX_SORT hint on a local partitioned index might result in poor performance, especially when you order by score. All hits to the query across all partitions must be obtained before the results can be sorted.

Instead, use an inline view when you use the DOMAIN_INDEX_SORT hint. Specifically, use the DOMAIN_INDEX_SORT hint to improve query performance on a local partitioned index under the following conditions:

• The Oracle Text query itself, including the order by SCORE() clause, is expressed as an in-line view.

• The Oracle Text query inside the in-line view contains the DOMAIN_INDEX_SORT hint.

• The query on the in-line view has a ROWNUM predicate that limits the number of rows to fetch from the view.

For example, the following Oracle Text query and local Oracle Text index are created on a partitioned doc_tab table:

     select doc_id, score(1) from doc_tab 
        where contains(doc, 'oracle', 1)>0 
        order by score(1) desc;

If you are interested in fetching only the top 20 rows, you can rewrite the query as follows:

     select * from 
          (select /*+ DOMAIN_INDEX_SORT */ doc_id, score(1) from doc_tab 
              where contains(doc, 'oracle', 1)>0 order by score(1) desc) 
      where rownum < 21;

Oracle Text provides a cache layer called the query filter cache that you can use to cache the query results. The query filter cache is sharable across queries. Multiple queries can reuse cached query results to improve the query response time.

Use the ctxfiltercache operator to specify which query results to cache. The following example uses the operator to store the results of the common_predicate query in the cache:

select * from docs where contains(txt, 'ctxfiltercache((common_predicate), FALSE)')>0;

In this example, the cached results of the common_predicate query are reused by the new_query query, to improve the query response time.

select * from docs where contains(txt, 'new_query & ctxfiltercache((common_predicate), FALSE)')>0; 

Oracle Text provides the BIG_IO option for improving the query performance for the CONTEXT indexes that extensively use IO operations.

The query performance improvement is mainly for data stored on rotating disks, not for data stored on solid state disks.

When you enable the BIG_IO option, a CONTEXT index creates token type pairs with one large object (LOB) data type for each unique token text. Tokens with the same text but different token types correspond to different rows in the $I table.

The indexes with the BIG_IO option enabled should have the token LOBs created as SecureFile LOBs, so that the data is stored sequentially in multiple blocks. This method improves the response time of the queries, because the queries can now perform longer sequential reads instead of many short reads.

To create a CONTEXT index with the BIG_IO index option, first create a basic storage preference by setting the value of its BIG_IO storage attribute to YES, and then specify this storage preference while creating the CONTEXT index.

The following example creates a basic mystore storage preference and sets the value of its BIG_IO storage attribute to YES:

exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'YES');

To disable the BIG_IO option, update the existing storage preference (mystore) by setting the value of its BIG_IO storage attribute to NO, and then rebuild the index.

exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'NO');
alter index idx rebuild('replace storage mystore');

To enable the BIG_IO option for a partitioned index without rebuilding the index, modify the basic storage preference by setting the value of its BIG_IO storage attribute to YES, replace the global index metadata using ctx_ddl.replace_index_metadata, and then call optimize_index in REBUILD mode for each partition of the partitioned index table.

The following example enables the BIG_IO option for the idx partitioned index:

exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'YES');
exec ctx_ddl.replace_index_metadata('idx', 'replace metadata storage mystore');
exec ctx_ddl.optimize_index('idx', 'rebuild', part_name=>'part1');

Oracle Text provides the SEPARATE_OFFSETS option to improve the query performance for the CONTEXT indexes that use IO operations, and whose queries are mainly single-word or Boolean queries.

The SEPARATE_OFFSETS option creates a different postings list structure for the tokens of type TEXT. Instead of interspersing docids, frequencies, info-length (length of the offsets information), and the offsets in the postings list, the SEPARATE_OFFSETS option stores all docids and frequencies at the beginning of the postings list, and all info-lengths and offsets at the end of the postings list. The header at the beginning of the posting contains the information about the boundary points between docids and offsets. Because separation of docids and offsets reduces the time for the queries to read the data, it improves the query response time.

To create a CONTEXT index with the SEPARATE_OFFSETS option, first create a basic storage preference by setting the value of its SEPARATE_OFFSETS storage attribute to T. Next, specify this storage preference when you create the CONTEXT index.

The following example creates a basic mystore storage preference and sets the value of its SEPARATE_OFFSETS storage attribute to T:

exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'T');

To disable the SEPARATE_OFFSETS option, update the existing storage preference (mystore) by setting the value of its SEPARATE_OFFSETS storage attribute to F, and then rebuild the index.

exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'F');
alter index idx rebuild('replace storage mystore');

To enable the SEPARATE_OFFSETS option for a partitioned index without rebuilding the index, modify the basic storage preference by setting the value of its SEPARATE_OFFSETS storage attribute to T, replace the global index metadata by using ctx_ddl.replace_index_metadata, and then call optimize_index in REBUILD mode for each partition in the partitioned index table.

The following example enables the SEPARATE_OFFSETS option for the partitioned idx index:

exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'T');
exec ctx_ddl.replace_index_metadata('idx', 'replace storage mystore');
exec ctx_ddl.optimize_index('idx', 'rebuild', part_name=>'part1');

Oracle Text provides the STAGE_ITAB option for improving the query performance for CONTEXT and search indexes that extensively use insert, update, and delete operations for near real-time indexing.

The STAGE_ITAB option is the default index option only for search indexes.

If you do not use the STAGE_ITAB index option, then when you add a new document to the CONTEXT index, SYNC_INDEX is called to make the documents searchable. This call creates new rows in the $I table, and increases the fragmentation in the $I table. The result is deterioration of the query performance.

When you enable the STAGE_ITAB index option, the following happens:

• Information about the new documents is stored in the $G staging table, not in the $I table. This storage ensures that the $I table is not fragmented and does not deteriorate the query performance.

• The $H b-tree index is created on the $G table. The $G table and $H b-tree index are equivalent to the $I table and $X b-tree index.

Rows are merged automatically from the $G table to the $I table when the number of rows in $G exceeds the storage setting, STAGE_ITAB_MAX_ROWS (10K by default). You can also force an immediate merge of the rows from $G to $I by running index optimization in MERGE optimization mode.

To create a CONTEXT index with the STAGE_ITAB index option, first create a basic storage preference by setting the value of its STAGE_ITAB storage attribute to YES. Next, specify this storage preference when you create the CONTEXT index.

The following example creates a basic mystore storage preference and sets the value of its STAGE_ITAB storage attribute to YES:

exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'STAGE_ITAB', 'YES');

You can also enable the STAGE_ITAB index option for an existing nonpartitioned CONTEXT index by using the rebuild option of the ALTER INDEX statement.

alter index IDX rebuild parameters('replace storage mystore');

To disable the STAGE_ITAB option for a nonpartitioned CONTEXT index, update the existing storage preference (mystore) by setting the value of its STAGE_ITAB storage attribute to NO, and then rebuild the index.

exec ctx_ddl.set_attribute('mystore', 'STAGE_ITAB', 'NO');
alter index idx rebuild parameters('replace storage mystore');

This operation runs the optimization process by using the MERGE optimization mode and then drops the $G table.

The rebuild option of the ALTER INDEX statement does not work with the partitioned CONTEXT index for enabling and disabling the STAGE_ITAB option.

The following example enables the STAGE_ITAB option for the partitioned CONTEXT idx index:

alter index idx parameters('add stage_itab');

The following example disables the STAGE_ITAB option for the partitioned CONTEXT idx index:

alter index idx parameters('remove stage_itab');

The contents of $G were automatically moved to $I during index synchronization when $G had more than 1 million rows in Oracle Database 12c Release 2 (12.2) or 100K rows in Oracle Database Release 18c. Starting with Oracle Database Release 21c, the contents of $G are automatically moved to $I during index synchronization when $G has more than 10K rows by default. This value is controlled by the STAGE_ITAB_MAX_ROWS attribute of the STORAGE preference.

The query performance is deteriorated when $G table is too fragmented. To avoid deterioration, starting with Oracle Database Release 18c, Oracle Text provides automatic background optimize merge for every index or partition. To enable automatic background optimize merge, you must set the STAGE_ITAB storage preference attribute to TRUE, and you must create the index with a storage preference which uses the STAGE_ITAB attribute.

By default, if you had enabled STAGE_ITAB in indexes before you upgraded to Oracle Database Release 18c, then STAGE_ITAB_AUTO_OPT is not enabled. If STAGE_ITAB and AUTO_OPTIMIZE are enabled in existing indexes, then you must disable AUTO_OPTIMIZE before you enable STAGE_ITAB_AUTO_OPT. Starting with Oracle Database Release 19c, STAGE_ITAB_AUTO_OPT is set to TRUE by default for automatic background optimize merge. If you set STAGE_ITAB_AUTO_OPT to FALSE, the merge is run as part of SYNC INDEX. It is recommended to set STAGE_ITAB and STAGE_ITAB_AUTO_OPT to TRUE instead of using AUTO_OPTIMIZE.

The following example creates a basic mystore storage preference and sets the value of its STAGE_ITAB_AUTO_OPT storage attribute to TRUE:

exec ctx_ddl.create_preference('mystore', 'basic_storage');
exec ctx_ddl.set_attribute('mystore', 'stage_itab', 'TRUE');
exec ctx_ddl.set_attribute('mystore', 'stage_itab_auto_opt', 'TRUE');
exec ctx_ddl.set_attribute('mystore', 'stage_itab_parallel', 16);