oracle global index vs local index on a partitioned table 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
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.