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.
Best Answer
You cannot convert a global index into a local index with an
ALTER INDEX
command. You need to drop the index and recreate it using theLOCAL
keyword.