Improved Response Time Using the Local Partitioned CONTEXT Index

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.