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. Join them; it only takes a minute:

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 big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index.

So I have idea to use INVISIBLE local INDEX (calculating for 12-16h) and then drop global index and make new index visible and rename it to just dropped global index.


SQL> create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) local invisible online;
create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) local invisible online
ERROR at line 1:
ORA-01408: such column list already indexed

Is there any other option to change global index to local using atomic/fast operations to change id on the fly?

Dropping global index and then creating new one is NOT acceptable.

share|improve this question

migrated from Sep 16 '12 at 20:11

This question came from our site for professional and enthusiast programmers.

can You, Sir, be more specific? Dropping partition makes global index unavailable. After dropping that global index i have couple o seconds when new local must be up and running – WBAR Jun 4 '12 at 11:19
Global indexes won't be unusable after a partition drop if you specify UPDATE GLOBAL INDEXES. This doesn't answer your main question (transform a global index into a local index) but might help you with dropping partitions. – Vincent Malgrat Jun 4 '12 at 11:26
UPDATE GLOBAL INDEXES how long it takes ? Like creating new one ? – WBAR Jun 4 '12 at 11:49
Only solution I found is when system has the low load, I executed: 1) Dropped global index 2) Dropped unused / archived partitions 3) Created local index with adding ONLINE word at end forcing to index be calculated on the fly without blocking my table. – WBAR Jul 9 '12 at 6:50
up vote 0 down vote accepted

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

tablespace IDX_TABLESPACE local unusable;

Then I need to rebuild index online choosing partition:


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.

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.