Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this column. And can you explain why?


Sean Nguyen

share|improve this question
up vote 2 down vote accepted

Why would you want to index CREATED_TIME if you're already partitioning on CREATED_TIME? If you are regularly querying the data looking for date ranges much smaller than your partition grain, that is, you're regularly querying for date ranges of a couple hours, you probably want to adjust your partitioning strategy to create partitions more frequently. If you query recent data in smaller intervals than old data-- for example, you regularly aggregate by hour over the past day, aggregate by day over the past week, and aggregate by month over the past year-- you may want to merge smaller partitions together as they age (potentially in addition to compressing older partitions).

Additionally, are you sure that you would want to index CREATED_TIME rather than creating a materialized view that pre-aggregates the data by some smaller interval than the partition? If you're aggregating data in various queries by hour, day, and month, for example, you'd generally be better served by creating a materialized view that pre-aggregated the data at the smallest grain (hour) and an Oracle dimension object that allowed query rewrite to use the materialized view to aggregate the hourly rows into daily or monthly results rather than trying to read all the data from the base table.

share|improve this answer
I partition my data to be the most common query for that time (30 days). And you are right. I don't need to index on create_time any more. There is no benefit. Just to be clear, i also have another index (Create_Time, ColumnA, ColumnB). Now with partition, i only need to do local index on (ColumnA, Column B)? – Sean Nguyen Jan 13 '12 at 19:52
I'd be careful about very granular partitions. Any query which spans multiple partitions uses statistics at the table, not partition level. The same holds true for subpartitions. – Adam Musch Jan 17 '12 at 20:07

A global index on created_time does not seem to be warranted on a table that is also partitioned on created_time, no matter what the size of the intervals would be.

First, you already would gain the benefits of partition pruning for any query that makes use of a query for created_time for a specific range.

Once you have limited the partitions queried, it would be optimal to work on the data that is local to those partitions. Generally it is optimal, to have queries that only have to select from individual partitions so that multiple 'tables' do not have to be joined for satisfying the query.

I would advise if there is a need for more granular access to created_time within any given range selected (minute, hour, day, month, year), that you create the index as a local index, available in each partition. If you needed to do continued aggregation or range queries within each individual partition that could be done.

This would also allow you to age out older partitions as necessary for data that can be archived, easily by exchanging partitions with empty tables or dropping partitions outright.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.