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 from each are combined as necessary to produce the final result set.
You create the CONTEXT index using the LOCAL keyword as follows:
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:
This is a query that 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.
This is a query that requires only the first n hits, and that the ORDER BY clause names the partition key. Consider an ORDER BY query on a price column to fetch the first 20 hits such as:
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.