How can local partition indexes help?

Answer: You can create local partitioned CONTEXT indexes on partitioned tables. This means that, on a partitioned table, each partition has its own set of index tables. Effectively, the results from the multiple indexes are combined as necessary to produce the final result set.

Use the LOCAL keyword to create the index:

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

With partitioned tables and local indexes, you can improve performance of the following types of CONTAINS 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.

• ORDER BY Partition Key Column: This query requires only the first n hits, and the ORDER BY clause names the partition key.

  See Also: “Improved Response Time using Local Partitioned CONTEXT Index”