CODEWITHSUNDEEP
oracle-databaseindexingpartitioning
Caitlin M. Shaw
Caitlin M. Shaw

Reputation: 89

Is there an advantage to using a local index on a partitioned table in Oracle?

I assume the answer is "no" in this scenario, but I figured I'd ask and see if there was something I was missing:

I have an Oracle table which is partitioned for ease of data loading -- data is loaded into six separate tables and then partition-switched into the main table. The only thing differentiating these loading tables is the source of the data, so each one has a unique datasource column which is used to partition the main table. We occasionally have some ad hoc queries which look at this datasource in the main table, but the standard reports querying this table ignore this column entirely. Nothing insert/update/deletes individual records from this table, so there's no concern about updating any indexes.

In this case, is there any reason to use local indexes instead of global ones?

Upvotes: 2

Views: 1021

Answers (1)

user330315
user330315

Reputation:

A local index makes a lot of sense - if you use partitioning for performance reasons.

If your queries always contain the partition key then a Oracle will only scan that specific partition (that is known as "partition pruning").

If you then have additional conditions that would benefit from an index lookup, the database only needs to check the local index which is much smaller then a global index and thus the lookup will be faster.

In your case, if you never (or almost never) include the partition key in the queries, you are right that the local index wouldn't be helpful.

Upvotes: 5

Related Questions