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 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.

But..

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
    
    
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
show 1 more comment

migrated from stackoverflow.com Sep 16 '12 at 20:11

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

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
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.