Take the 2-minute tour ×
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.

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?

Thanks,

Sean Nguyen

share|improve this question
add comment

1 Answer 1

up vote 1 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
add comment

Your Answer

 
discard

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.