convert Global index to local index in oracle

vkchaitu82
vkchaitu82 used Ask the Experts™
on
Hi experts,

How do i convert non unique global index which has data to a local index on a partitioned table without dropping and creating the index. Any thoughts??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
Sorry, but you can't.

Author

Commented:
The problem is our DB developers created all indexes as GLOBAL. when we are dropping the partitions the indexes are getting invalid and we need to rebuild them everytime which we want to avoid.

So, the only option is drop and recreate as LOCAL index right??
Most Valuable Expert 2011
Top Expert 2012
Commented:
right,

you can't even do a rebuild, because a locally partitioned index can't be rebuilt in it's entirety but only one partition at a time

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_1010.htm#i2050526

but that's a catch-22 since you can't get local partitions without a rebuild.

So, you'll have to drop and recreate
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009

Commented:
sdstuber is 100% correct so please give credit to his answer.

It is important to understand why. You must understand the concept of Oracle "segments".

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/logical.htm

A regular table is a single segment.
A regular index is a single segment.
A partitioned table is multi-segment (each partition is a segment).
A global index is a single segment (though it indexes multiple table segments).
A locally partitioned index is multi-segment (a segment goes with each partition).

A segment cannot span tablespaces. A partitioned object (partitioned table or locally partitioned index) may span tablespaces (each segment must be whole within a tablespace).

So your question is in essence "Can I split a single segment object into a multi-segment object".

And the answer is as stated, it takes a rebuild to do this, as the datablocks & extents must physically be re-organized and assigned to different segments.

Author

Commented:
Thanks for the response..

Can you give me some pros and cons of having LOCAL vs GLOBAL index on PK (while quering , DML operations) . I am aware that the partition key should be part of PK to be LOCAL index.
Most Valuable Expert 2011
Top Expert 2012

Commented:
You've already discovered the big one.

If you need to manipulate (in your case, drop) a table partition then the entire global index will need to be rebuilt where as a local is, well, "localized" to just that partition.

Most Valuable Expert 2011
Top Expert 2012

Commented:
Note in 11g you can have reference partitioning which allows local partitions based on the partition key of a different table.

For instance,  a table of orders partitioned by shipping month

the order lines could be reference partitioned by shipping month even though they don't actually have a shipping month column in them.

This may or may not apply to your specific example.  Only mentioned it because of your previous post about "required" partition keys

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial