Oracle global index vs local index on a partitioned table

indexoraclepartitioning

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

Best Answer

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.

Related Solutions

The performance difference between local text index and domain text index in oracle

Sean, I believe that the local text index is part of the Oracle Text feature of Oracle that is specific to working with documents, such as Web sites, digital libraries, document or warehouses. While the Domain Indexes are indexes that we use in day-to-day.

The following documentation will be helpful: About Oracle Text: http://docs.oracle.com/cd/B28359_01/text.111/b28303/overview.htm (In the third chapter there is information about the indexes)

About the Domain Indexes: http://docs.oracle.com/cd/B10500_01/appdev.920/a96595/dci07idx.htm

Oracle 11: Change global index to local on huge production table

I found solution making creating index quite faster... (on Oracle 11g works fine)

drop index I_EMPLOYEE_SALARY_T;
create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) 
tablespace IDX_TABLESPACE local unusable;

Then I need to rebuild index online choosing partition:

ALTER INDEX I_EMPLOYEE_SALARY_T REBUILD PARTITION partition_name online;

Altering index is much faster because it using different, faster mechanism and data can be accessed much sooner. For example when data for employees (in this example) is partitioned by timestamp. So newer data are more quickly available.

Best Answer

Related Solutions

The performance difference between local text index and domain text index in oracle

Oracle 11: Change global index to local on huge production table

Related Question