5

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.

Improve this question
6
  • Have you tried ALTER TABLE... DROP PARTITION ... UPDATE GLOBAL INDEXES?
    – Vincent Malgrat
    Commented Jun 4, 2012 at 11:11
  • 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
    Commented Jun 4, 2012 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
    Commented Jun 4, 2012 at 11:26
  • UPDATE GLOBAL INDEXES how long it takes ? Like creating new one ?
    – WBAR
    Commented Jun 4, 2012 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
    Commented Jul 9, 2012 at 6:50

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.